PostgreSQL 9.2 – SELECT Records Based on COUNT Query

countpostgresql-9.2

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of occurrences and not the actual records.
For example, if I have a table of "Names":

Id   Name        City
1    Mike        NYC
2    Bob         DC
3    Rachel      DC
4    Mike        NYC
5    Bob         DC
6    Melissa     LA
7    Melissa     LA

…and I want to get the count of each name, I understand that I use the SQL:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name;

The following would be returned:

Name       Count
Bob        2
Mike       2
Rachel     1
Melissa    2

Now, If I want to get the count of each name where the count is more than 1, I do this:

SELECT Name, COUNT(Name) AS "Count"
FROM Names
GROUP BY Name
HAVING COUNT(Name) > 1;

The following would be returned:

Name       Count
Bob        2
Mike       2
Melissa    2

Now, what I want to do is return all the columns for each value if they occur more than once. I thought about doing a join on the table after doing the counts, but that seems like an expensive operation when I tried it a few times. The more columns I added, I noticed that it was comparing those too, which is not what I want. What I'd like is the result below. 

Name       City    Count
Bob        DC        2
Mike       NYC       2
Melissa    LA        2

I know there should be an easy way to do this, but I'm just not finding it. Thanks!

Best Answer

This can be done using a window function:

select name, city, cnt
from (
  select name, city, count(*) over (partition by name) as cnt
  from names
) t
where cnt > 1;
Related Question