Let's consider this table
-
select * from game;
-
-
id | name | grade | city
-
----+--------+-------+-------------
-
1 | john | A | boston
-
2 | mike | A | boston
-
3 | leo | B | LA
-
4 | vik | C | LA
-
5 | carrie | C | NY
-
6 | john | A | cleveland
-
7 | mary | B | boston
-
8 | van | B | boston
-
9 | rose | B | springfield
-
10 | dan | C | NY
-
11 | mark | C | boston
-
12 | linda | B | NY
-
13 | max | C | NY
-
(13 rows)
-
In order to get what we need we will use the GROUP BY clause:
-
SELECT grade, city from game GROUP BY grade, city HAVING COUNT(*) > 1;
-
-
grade | city
-
-------+--------
-
A | boston
-
B | boston
-
C | NY
-
(3 rows)
-
This gives us some idea on which combinations of grade and city should be included in the output.
However this is not the result we need, but rather something we can use
for a subquery, or perhaps for a join condition:
-
SELECT G1.* from game G1 JOIN
-
(select grade, city from game group by grade, city having count(*) > 1) AS G2
-
using (grade, city) order by grade, city;
-
-
id | name | grade | city
-
----+--------+-------+--------
-
1 | john | A | boston
-
2 | mike | A | boston
-
7 | mary | B | boston
-
8 | van | B | boston
-
13 | max | C | NY
-
10 | dan | C | NY
-
5 | carrie | C | NY
-
(7 rows)
-
-
The result above includes rows with the same grade and city.
I would suggest these links for further reading:
SELECT (group by) Table Expressions Aggregate Functions 
