CODEWITHSUNDEEP
sqlsql-server
Usher
Usher

Reputation: 2146

Find duplicates in two columns in a table

I just browse this post and tried all the scripts but still i am not getting what i am expecting.

Here is my table 

Name        email
BRIAN MAT   [email protected]
BRIAN MAT   BRIAN [email protected]
AMY Lee     [email protected]
AMY.Lee     [email protected]
Madison Taylor  [email protected]


SELECT Name 
FROM Employee
GROUP BY Name 
HAVING ( COUNT(Name > 1 )

result

BRIAN MAT

SELECT email
FROM Employee
GROUP BY email
HAVING ( COUNT(email> 1 )

Result

[email protected]

I was trying to group this two script but it shows blank 

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

Please correct me what i am missing in my script to acheive the result like below

Name        email
BRIAN MAT   [email protected]
BRIAN MAT   BRIAN [email protected]
AMY Lee     [email protected]
AMY.Lee     [email protected]

Upvotes: 0

Views: 1659

Answers (3)

Larry Beasley
Larry Beasley

Reputation: 334

The least complicated. A quick and dirty solution.

SELECT 
    a.name, 
    a.email, count(*)
FROM
    employee a

INNER JOIN
    employee b on b.name = a.name or b.email = a.email
GROUP BY a.name, a.email
HAVING COUNT(*) > 1

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

You could use windowed COUNT:

WITH cte  AS (
   SELECT *,
         COUNT(*) OVER(PARTITION BY name)  AS c_name,
         COUNT(*) OVER(PARTITION BY Email) AS c_email
   FROM Employee
)
SELECT name, email
FROM cte
WHERE c_name > 1 OR c_email > 1;

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT *
FROM Employee
WHERE Name IN (SELECT Name 
               FROM Employee
               GROUP BY Name 
               HAVING COUNT(Name > 1) 
              )
   OR Email IN (SELECT email
                FROM Employee
                GROUP BY email
                HAVING COUNT(email> 1)
              )

Upvotes: 2

Related Questions