Find duplicates in two columns in a table

Question

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   BRIAN.MAT@abc.Com
BRIAN MAT   BRIAN MAT@abc.Com
AMY Lee     AMY.Lee@abc.Com
AMY.Lee     AMY.Lee@abc.Com
Madison Taylor  Madison.Tyl@abc.com


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

AMY.Lee@abc.Com

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   BRIAN.MAT@abc.Com
BRIAN MAT   BRIAN MAT@abc.Com
AMY Lee     AMY.Lee@abc.Com
AMY.Lee     AMY.Lee@abc.Com

Grouping by both name and email will mean when name and email are both the same then return the result. Either do this as a union (first query union all second query) or create subqueries (as per Juan Carlos's answer) — Twelfth, Nov 15 '17 at 19:49

Lukasz Szozda · Answer 1 · 2017-11-15T20:51:34.007

3

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;

edited Nov 15 '17 at 20:51

answered Nov 15 '17 at 19:51

Lukasz Szozda

162,964
23
234
275

i was curious how the script work and i tried it but "Conversion failed when converting the varchar value 'AMY.LEE@abc.com' to data type int. – Usher Nov 15 '17 at 20:31
@Usher c_name or c_email are aliases for COUNT which returns number. Are you sure that you don't use something like `name > 1`? – Lukasz Szozda Nov 15 '17 at 20:51

score 2 · Accepted Answer · answered Nov 15 '17 at 19:48

2

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)
              )

answered Nov 15 '17 at 19:48

Juan Carlos Oropeza

47,252
12
78
118

Thank you Juan, i was clueless how to get the expected result – Usher Nov 15 '17 at 20:33

Larry Beasley · Answer 3 · 2017-12-01T15:39:58.857

1

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

edited Dec 01 '17 at 15:39

answered Nov 15 '17 at 20:39

Larry Beasley

334
1
9

Find duplicates in two columns in a table

3 Answers3