CODEWITHSUNDEEP
mysqlsql
user1188015
user1188015

Reputation: 13

Using sql, how do get rows with count for duplicates?

How can I count duplicates rows (where the date and names are the same) using a select statement?

Here is my table

id  date        name
1   01/02/12    sam  
2   01/02/12    john  
3   02/04/12    eddie  
4   01/06/12    joe 
5   01/02/12    john  
6   01/02/12    john
7   02/04/12    eddie
8   01/05/12    eddie
9   01/07/12    joe

Result should be like this:

id  date        name   count
1   01/02/12    sam    1
    01/02/12    john   3
    02/04/12    eddie  2
4   01/06/12    joe    1
8   01/05/12    eddie  1
9   01/07/12    joe    1

I need a third coloumn in result set which value will be count column. also i dont need the id if the count is more than 1 (i think that would be impossible anyways). I am using mysql.

Thanks for advice.

Upvotes: 1

Views: 297

Answers (3)

aF.
aF.

Reputation: 66757

select date, name, count(id) as counter
from mytable
group by date, name
having count(id) > 1

Upvotes: 0

ruakh
ruakh

Reputation: 183612

You can write:

SELECT MIN(id) AS id, date, name, COUNT(1) AS `count`
  FROM table_name
 WHERE ...
 GROUP BY date, name
;

That will always give the least id of the group. If you specifically want the first field to be NULL when there are duplicates, then you can change MIN(id) to CASE WHEN COUNT(1) > 1 THEN NULL ELSE MIN(id) END, but it sounds like you don't care about that?

Upvotes: 5

Randy
Randy

Reputation: 16673

something like this:

select  id, date, name, count(*)
from mytable
group by id, date, name
having count(*) > 1

Upvotes: 0

Related Questions