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 —
x
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