Take the 2-minute tour ×

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.

share|improve this question
Check out this: stackoverflow.com/questions/6138518/count-in-sql –  Nick Vaccaro Feb 3 '12 at 17:08

3 Answers 3

up vote 5 down vote accepted

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?

share|improve this answer
I like this method, seems to be the clearest answer, and doesn't require a subquery. –  N West Feb 3 '12 at 17:13
And you can also get all the ids (when duplicates) adding this in the Select list: GROUP_CONCAT(id) AS allId –  ypercube Feb 3 '12 at 17:26
super!.. thanks.. i will try soon as i get home.. –  user1188015 Feb 3 '12 at 17:43
Also, you don't actually need MIN(id). MySQL will let you just use id to get the ID of one of the rows in the group (it doesn't guarantee which one). Some other RDBMSes are more pedantic about that, though. –  Ilmari Karonen Feb 3 '12 at 22:58
@IlmariKaronen: I'm well aware of that feature, but I make a point of neither using nor recommending it. I'm pretty sure its main purpose is to confuse and mislead. (Exception: if I'm JOINing, and GROUPing BY one table's primary key, then I sometimes take advantage of that feature for the other columns. But that's it.) –  ruakh Feb 3 '12 at 23:03

something like this:

select  id, date, name, count(*)
from mytable
group by id, date, name
having count(*) > 1
share|improve this answer
This won't count duplicates, since you're grouping by id... –  N West Feb 3 '12 at 17:07
That doesn't make sense. id seems to be the primary key; if you GROUP BY it, then COUNT(*) will always be 1. (And even once you fix that -- the OP's sample results do include non-duplicate rows, so the HAVING clause is wrong.) –  ruakh Feb 3 '12 at 17:07
select date, name, count(id) as counter
from mytable
group by date, name
having count(id) > 1
share|improve this answer

Your Answer


