SQL query, count and group by

Question

If I have data like this:

What is the SQL query to bring back the records where Name is repeated 2 or more times?

So the result I would want is

Tom
Dan

marcolz · Accepted Answer · 2019-10-07 07:39:44Z

40

Couldn't be simpler...

Select Name, Count(Name) As Count 
    From Table
    Group By Name
    Having Count(Name) > 1
    Order By Count(Name) Desc

This could also be extended to delete duplicates:

Delete From Table
Where Key In (
    Select Max(Key)
        From Table
        Group By Name
        Having Count(Name) > 1
    )

marcolz

2,9902 gold badges27 silver badges30 bronze badges

answered Aug 6, 2008 at 9:02

GateKiller

76.3k75 gold badges176 silver badges204 bronze badges

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

1 Comment

For delete, you should leave only the Min(key) and delete other entries. Query should be modified as "...where Key NOT IN ( select Min(Key) ...."

Ryan · Accepted Answer · 2008-08-17 04:36:53Z

4

select name from table group by name having count(name) > 1

answered Aug 17, 2008 at 4:36

Ryan

4,6828 gold badges40 silver badges43 bronze badges

Dag Haavi Finstad · Accepted Answer · 2008-08-10 01:31:05Z

2

This could also be accomplished by joining the table with itself,

SELECT DISTINCT t1.name
FROM    tbl t1
        INNER JOIN tbl t2
        ON      t1.name = t2.name
WHERE   t1.key         != t2.key;

answered Aug 10, 2008 at 1:31

Dag Haavi Finstad

292 bronze badges