Richard Richard - 3 months ago 5
SQL Question

Column to identify duplicates

I've got a table of data, and am trying to identify where duplicate values exist. I can write a query which only returns duplicates, but I'm after a query to return all records, and add a 'Count' column to show how many times it occurs

For example,

ID Name Surname Email
1 Adam Smith a.smith@domain.com
2 Bob Clark b.clark@domain.com
3 Andy Smith a.smith@domain.com


And I want a query to return:

ID Name Surname Email Count
1 Adam Smith a.smith@domain.com 2
2 Bob Clark b.clark@domain.com 1
3 Andy Smith a.smith@domain.com 2


Thanks.

Answer

Assuming you consider a duplicate to be a matching email, then this one will work:

SELECT T.id, T.name, T.surname, T.email, sub.count
FROM T
LEFT JOIN
(SELECT email, COUNT(*) AS count
FROM T
GROUP BY email) sub
ON T.email = sub.email

If you want both surname and email to match to be considered a duplicate, try this:

SELECT T.id, T.name, T.surname, T.email, sub.count
FROM T
LEFT JOIN
(SELECT email, surname, COUNT(*) AS count
FROM T
GROUP BY email, surname) sub
ON T.email = sub.email
AND T.surname = sub.surname

Tested here: http://sqlfiddle.com/#!9/84c041/2