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

Question

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.

Check out this: stackoverflow.com/questions/6138518/count-in-sql — Nick Vaccaro
– Nick Vaccaro, Commented Feb 3, 2012 at 17:08

ruakh · Accepted Answer · 2012-02-03 17:06:18Z

5

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?

answered Feb 3, 2012 at 17:06

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

N West Over a year ago

I like this method, seems to be the clearest answer, and doesn't require a subquery.

ypercubeᵀᴹ Over a year ago

And you can also get all the ids (when duplicates) adding this in the Select list: GROUP_CONCAT(id) AS allId

Ilmari Karonen Over a year ago

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.

ruakh Over a year ago

@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.)

Randy · Accepted Answer · 2012-02-03 17:05:57Z

0

something like this:

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

answered Feb 3, 2012 at 17:05

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

ruakh Over a year ago

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.)

aF. · Accepted Answer · 2012-02-03 17:08:01Z

0

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

answered Feb 3, 2012 at 17:08

aF.

67k45 gold badges141 silver badges201 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related