CODEWITHSUNDEEP
sql
user2135808
user2135808

Reputation: 81

Finding duplicate email addresses

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.

Upvotes: 8

Views: 30884

Answers (8)

Aditya Raj
Aditya Raj

Reputation: 1

SELECT Email, COUNT(*) as count
FROM TableName
GROUP BY Email
HAVING count(*) > 1;

Upvotes: 0

Shalini Chandra
Shalini Chandra

Reputation: 1

No need to put the tableName in selecting columns

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

Upvotes: 0

Praveen Yadav
Praveen Yadav

Reputation: 11

About all posted answers here, using Group by methods. This query can also be written with self join method.

select distinct f.email
from Name f
join Name s
where f.id <> s.id and f.email = s.email

Upvotes: 1

Shubham Prasad
Shubham Prasad

Reputation: 21

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.

Upvotes: 2

Aspirant
Aspirant

Reputation: 2278

 select id,email from 
 (select id,email,count(email) over (partion by email order by id) cnt from name) where cnt>1

Upvotes: 0

John Woo
John Woo

Reputation: 263713

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

Upvotes: 15

Luis Tellez
Luis Tellez

Reputation: 2973

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
)

Upvotes: 4

Vishal Suthar
Vishal Suthar

Reputation: 17194

Here you go:

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

Upvotes: 1

Related Questions