Skip to content
Advertisement

How do I write a case statement to return duplicate records?

I have a table of names and addresses and want to return the full list of names and addresses with an extra column that identifies if the address is a duplicate or unique address. There are several duplicate address entries that I can see when I run a more simple statement —

SELECT PersonAddressLine1, Count (*)
FROM CompanyTable1
GROUP BY PersonAddressLine1

or

SELECT PersonAddressLine1, Count (*)
FROM CompanyTable1
GROUP BY PersonAddressLine1
HAVING COUNT (*) > 1

but I need to return the full table with columns –

PersonID, PersonName, PersonAddressLine1, AddressVerification (Duplicate / Unique), CompanyID, CompanyName.

PersonID - 1
PersonID - 2
PersonID - 3
PersonName - Ryan
PersonName - Andrew
PersonName - Ben
PersonAddressLine1 - 100 Avenue
PersonAddressLine1 - 100 Avenue
PersonAddressLine1 - 200 Avenue

If I use the below case statement, the group condition impacts the PersonAddressLine1 count result. It shows the addresses all as unique.

SELECT PersonID, PersonName, PersonAddressLine1, CompanyID, CompanyName, 

CASE WHEN count(*) > 1 
THEN CONCAT(PersonName,' ','Address Line 1 is duplicate')
    ELSE CONCAT(PersonName,' ','Address Line 1 is unique')
END AS 'Person Address Verification'

FROM CompanyTable1

GROUP by PersonAddressLine1, PersonID, PersonName, CompanyID, CompanyName

Advertisement

Answer

you can use window function COUNT(*) OVER (PARTITION BY PersonAddressLine1) to count the no of duplicates

SELECT PersonID, PersonName, PersonAddressLine1, CompanyID, CompanyName, 
       CASE WHEN COUNT(*) OVER (PARTITION BY PersonAddressLine1) > 1 
            THEN CONCAT(PersonName,' ','Address Line 1 is duplicate')
            ELSE CONCAT(PersonName,' ','Address Line 1 is unique')
            END AS 'Person Address Verification'
FROM   CompanyTable1

what your current query is doing right now is not only checking for PersonAddressLine1 but all of the following PersonAddressLine1, PersonID, PersonName, CompanyID, CompanyName

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement