Count 'unique' duplicates in SQL

Question

I am pretty new to SQL.

When I have a list like this

+----+------+
| id | name |
+----+------+
| 1  | Mike |
+----+------+
| 1  | Mike |
+----+------+
| 2  | Mike |
+----+------+
| 2  | Mike |
+----+------+
| 2  | Mike |
+----+------+
| 5  | Sam  |
+----+------+
| 6  | Sam  |
+----+------+
| 7  | Sam  |
+----+------+
| 7  | Sam  |
+----+------+

and I want to count how many unique Mikes and Sams there are. So the output would be

+------+---+
| Mike | 2 |
+------+---+
| Sam  | 3 |
+------+---+

How would I do that?

Edit:

This was just an example for my problem. The code would be something like SELECT id, name FROM id, name WHERE some conditions

And I tried something like SELECT id, name, COUNT(*) From id,name WHERE some conditions GROUP BY id, name

and the output is not what I want. It just counts how many Mikes and Sams there are. But I want how many unique Mikes and Sams there are

Sorry I am not sure what you mean. I got this with a Select statement of course — A. Aoe, Jul 22 '17 at 20:27
Try SELECT with GROUP BY and share what you have tried with us. — Kemal Güler, Jul 22 '17 at 20:29
Well this case was just an example. It would be SELECT id, name FROM id, name WHERE some conditions — A. Aoe, Jul 22 '17 at 20:29
Possible duplicate of [SQL Server query - Selecting COUNT(\*) with DISTINCT](https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct) — , Aug 16 '17 at 15:22
Please read [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) before attempting to ask more questions. — , Aug 16 '17 at 15:22

user2722968 · Accepted Answer · 2017-07-22T20:31:59.220

1

SELECT name, COUNT(DISTINCT id) AS cnt
FROM mytable
GROUP BY name
HAVING COUNT(DISTINCT id) > 1

The syntax may vary for your database server but the point will be the same: Count the number of unique ID values per name, filter those that have more than one.

If your database server does not have COUNT DISTINCT:

SELECT name, COUNT(*) AS cnt
FROM (SELECT DISTINCT id, name
      FROM mytable)
GROUP BY name
HAVING COUNT(*) > 1

edited Jul 22 '17 at 20:31

answered Jul 22 '17 at 20:29

user2722968

13,636
2
46
67

Why `HAVING COUNT(DISTINCT id) > 1` ? – Oto Shavadze Jul 22 '17 at 20:31
I'm assuming that, while not shown in the example but suggested in the topic provided by op, he is after duplicates. Those will have a `COUNT(DISTINCT id)` greater than one. – user2722968 Jul 22 '17 at 20:33
Ahh that nearly is it! Just without the HAVING clause. I needed that Distinct, totally didn't think of that. Thank you very much – A. Aoe Jul 22 '17 at 20:38

Count 'unique' duplicates in SQL

1 Answers1