CODEWITHSUNDEEP
sqlsql-server-2008-r2aggregate-functions
NimChimpsky
NimChimpsky

Reputation: 47300

Find duplicates, display each result in sql

I want to write something like this :

select t.id, t.name, from table t
group by t.name having count(t.name) > 1

To produce the following :

id      name  count 
904834  jim    2
904835  jim    2
90145   Fred   3
90132   Fred   3
90133   Fred   3

Upvotes: 2

Views: 9533

Answers (6)

Edin Smajovikj
Edin Smajovikj

Reputation: 1

Similar to previous answers with less code. Tested on SQL Server 2008:

SELECT t.id, t.name,COUNT(*) 
FROM table t
GROUP BY t.id, t.name
HAVING COUNT(t.id) > 1

Upvotes: 0

Suresh Borade
Suresh Borade

Reputation: 1

Please Check it once .... in SQL Server 2008

SELECT   t.id, 
         t.NAME, 
         Count(t.id)   AS duplicate id, 
         count(t.NAME) AS duplicate names 
FROM     t 
GROUP BY t.id, 
         t.NAME 
HAVING   count(t.NAME) > 1

Upvotes: -2

AllisonC
AllisonC

Reputation: 3100

Assuming mysql (when I wrote the answer, I do not think the person specified the dbms) 

SELECT t.id, t.name, (SELECT COUNT(t2.name) FROM test t2 ) AS t_count
FROM  test t
HAVING t_count > 1;

Upvotes: 1

Raj More
Raj More

Reputation: 48024

If you remove the ID column then you can get all the names that have multiple entries

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

For each name, if you want the minimum or maximum id you can do this

select t.id, t.name, min (t.id) as min_id, max (t.id) as max_id
from table t
group by t.name
having count(t.name) > 1

For each name, if you want all the ids that are duplicates, then you have to use a subquery

select t.id, t.name
from table t
where name in
(
    select t1.name
    from table t1
    group by t1.name
    having count(t1.name) > 1
)

Upvotes: 3

Matt Smucker
Matt Smucker

Reputation: 5244

Just join the table to a subquery pulling the count for each name

SELECT t.ID, t.Name, d.Count
FROM #MyTable t
JOIN 
(
SELECT name, COUNT(*) as Count
FROM #MyTable 
GROUP BY Name
HAVING COUNT(*) > 1
) D 
ON t.Name = d.Name

Upvotes: 2

Lamak
Lamak

Reputation: 70668

For SQL Server 2005+, you can do the following:

SELECT *
FROM (SELECT id, Name, COUNT(*) OVER(PARTITION BY Name) [Count]
      FROM table) t
WHERE [Count]>1

Upvotes: 7

Related Questions