Distinct count across multiple columns?

• brett.davis1

  SSChasing Mays

  Points: 608

  More actions

  January 4, 2008 at 3:49 am

  #181906

  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

• Johannes Fourie

  Default port

  Points: 1470

  More actions

  January 4, 2008 at 4:29 am

  #764756

  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

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  January 4, 2008 at 5:43 am

  #764779

  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