Reputation: 13
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
Reputation: 66757
select date, name, count(id) as counter
from mytable
group by date, name
having count(id) > 1
Upvotes: 0
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
Reputation: 16673
something like this:
select id, date, name, count(*)
from mytable
group by id, date, name
having count(*) > 1
Upvotes: 0