January 4, 2008 at 3:49 am
SELECT DISTINCT Address1, Address2, City, Postcode, COUNT(Postcode)
FROM AddressTable
GROUP BY Postcode
HAVING COUNT(Postcode) > 1
Hi All.
Im trying to find any duplicate addresses by finding distinct matches across multiple columns... but it doesnt work.
When I run the above I get complaints that Address2 isnt in the aggregate or GROUP BY clause.
I'd appreciate your help on this one.
Many thanks,
Brett
January 4, 2008 at 4:29 am
you can do this:
SELECT PostCode, COUNT(Postcode) FROM
(
SELECT DISTINCT Address1, Address2, City, Postcode, Postcode
FROM AddressTable
) AS Sub
GROUP BY Postcode
HAVING COUNT(Postcode) > 1
January 4, 2008 at 5:43 am
Instead of what you had:
SELECT DISTINCT Address1, Address2, City, Postcode, COUNT(Postcode)
FROM AddressTable
GROUP BY Postcode
HAVING COUNT(Postcode)
Why not simply do this?
SELECT Address1,Address2,City,PostCode,Count(PostCode)
FROM AddressTable
GROUP BY Address1,Address2,City,PostCode
HAVING Count(PostCode) > 1
The GROUP BY clause will find distinct values of Address1, Address2, City & PostCode. That will show anything where all the values match.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply