Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Distinct count across multiple columns? Expand / Collapse
Author
Message
Posted Friday, January 4, 2008 3:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 30, 2010 9:39 AM
Points: 18, Visits: 80

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
Post #438842
Posted Friday, January 4, 2008 4:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:04 AM
Points: 77, Visits: 223
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
Post #438853
Posted Friday, January 4, 2008 5:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #438881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse