akshay bhat akshay bhat - 10 months ago 41
SQL Question

Merge and Duplicates

I have this Address table which has multiple columns. I want to concatenate the street number and street name column as Address and check if the address is unique.

SELECT ( street_num + ' ' + street_name ) AS Addr
FROM [propertyaddress_workfinal] AS t1
INNER JOIN (SELECT ( street_num + ' ' + street_name ) AS A2
FROM [propertyaddress_workfinal]) AS t2
ON t1.addr = t2.a2
WHERE Count(*) > 1

I get and error stating invalid column name 'Addr'.

Doing something silly here.


This is the way to do it

SELECT street_num + ' ' + street_name AS Addr 
FROM  [propertyaddress_workfinal]
GROUP BY street_num + ' ' + street_name
HAVING Count(*) > 1 

Though if the concatenated space isn't integral to your duplicate matching

GROUP BY street_num, street_name

Will perform better if you have a composite index on those two columns.