WolfieeifloW WolfieeifloW - 1 month ago 10
SQL Question

How can I write a query to show details of customers with duplicate names?

I have been looking around for quite a bit and cannot seem to find a solution that fits what I need.

I have a table with many, many customers and the issue is that there are many, many duplicates in this table.

I have been able to show the customers that have duplicate records and the count of how many are in the table with that same name, but now I am trying to split this up and show all of their information so we can confirm that the customer is the correct one when we look them up.

I was using this code:

SELECT COUNT(NAME), NAME
FROM DEV.ALL_CUSTOMER
GROUP BY NAME
HAVING COUNT(NAME) > 1;


Which gives results that show like this:

COUNT(NAME) | NAME
------------|-------------------
3 | Smith, John
2 | Doe, Jane
2 | Doe, Joe
2 | Smith, Jane


I then added in all of the informational fields I needed:

SELECT COUNT(NAME), NAME, TOTAL_PURCHASED, ADDRESS, CITY, STATE_PROV, POSTAL_CODE, COUNTRY, HOME_PHONE, WORK_PHONE, WORK_EXT, OTHER_PHONE, EMAIL_ADDRESS
FROM DEV.ALL_CUSTOMER
GROUP BY NAME, TOTAL_PURCHASED, ADDRESS, CITY, STATE_PROV, POSTAL_CODE, COUNTRY, HOME_PHONE, WORK_PHONE, WORK_EXT, OTHER_PHONE, EMAIL_ADDRESS
HAVING COUNT(NAME) > 1;


But this still has them grouped and does not show the information for each record:

COUNT(NAME) | NAME | TOTAL_PURCHASED | ADDRESS | CITY ...
------------|-------------|-----------------|---------|------- ..
3 | Smith, John | 0 | (null) | (null) ..
2 | Doe, Jane | 0 | (null) | (null) ..
2 | Doe, Joe | 0 | (null) | (null) ..


But I know for a fact that one of the five "John Smith" customers have purchased stuff.

Instead, I would like the results to come out like this:

NAME | TOTAL_PURCHASED | ADDRESS | CITY ...
------------|-----------------|---------------|------- ..
Smith, John | 250 | 123 Fake St. | (null) ..
Smith, John | 0 | (null) | Oshawa ..
Smith, John | 300 | (null) | Toronto .
Doe, Jane | 0 | (null) | (null) ..
Doe, Jane | 300 | 456 Fake St. | Toronto .
Doe, Joe | 11235 | (null) | (null) ..
Doe, Joe | 0 | (null) | (null) ..

Answer

Use window functions:

SELECT c.*
FROM (SELECT c.*, COUNT(*) OVER (PARTITION BY NAME) as cnt
      FROM DEV.ALL_CUSTOMER c
     ) c
WHERE cnt > 1
ORDER BY NAME;

This will give you the rows that are duplicated on NAME.

Comments