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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply