Distinct count across multiple columns?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Distinct count across multiple columns?

Distinct count across multiple columns?

Rate Topic

Display Mode

Topic Options

Author

Message

brett.davis1

Posted Friday, January 04, 2008 3:49 AM

Grasshopper

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

Johannes Fourie

Posted Friday, January 04, 2008 4:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 13, 2013 1:14 AM
Points: 77, Visits: 216

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

Grant Fritchey

Posted Friday, January 04, 2008 5:43 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281

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 »

Permissions