0

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!

1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.