Finding duplicate email addresses

Question

I need to find duplicate emails in our database. I am looking on one table for this information. What I have so far

 SELECT name.email, name.ID

 From Name
 Group BY Name.ID, Name.EMAIL
 Having Count(*) > 1

I know that its wrong, but not sure how to write it appropriately.

Answer 1

remove the ID

 SELECT name.email
 From   Name
 Group  BY Name.EMAIL
 Having Count(*) > 1

if you want to get the number of email,

 SELECT name.email, COUNT(*) totalEmailCount
 From   Name
 Group  BY Name.EMAIL
 Having Count(*) > 1

Answer 2

The query would be

SELECT name.email, COUNT(*) FROM Name 
GROUP BY Name.email HAVING COUNT(*) > 1

What you need to know is that if you group also by ID the count would be 1, thats why your query didn't work.

If you need to know the IDs of the users with emails duplicated you can do this:

select Name.ID, Name.Email from Name where Name.Email in (

SELECT name.email FROM Name 
    GROUP BY Name.email HAVING COUNT(*) > 1
)

Answer 3

The below SQL query will return the ID and EMAIL of the first matching row which contain the same(duplicate) EMAIL

select ID, EMAIL from Name group by EMAIL having count(EMAIL) > 1

If someone, wants all the duplicate EMAIL from the Name table then, he/she can execute the following SQL query

select EMAIL from Name group by EMAIL having count(EMAIL) > 1

Note that: SQL is a fully case-insensitive language.

Answer 4

Here you go:

SELECT name.email, COUNT(*)
FROM
    Name
GROUP BY
    Name.email
HAVING 
    COUNT(*) > 1