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