How to recognize rows that are should be considered the same?

1

Hello,

I have a question regarding how I can write my query so that it can recognize some rows that should be considered the same.

So I am tasked with finding out which Street/St appear the most frequent in my data base.

The thing is that in my database, for Melrose Street, it can be written as 'Melrose Street' or 'Melrose St', which both are the same just written differently. So I need to write a query so that it takes in consideration both 'Street' and 'St'

This is from my database

image

So far this is my query where I found the most frequently appeared street addresses, however, the total doesnt include both the 'street' and 'st' combined so they appear in separate rows.

SELECT street, count(street) as TotalRepetition
FROM dbo.address
GROUP BY street
ORDER BY TotalRepeat DESC

Thanks!

2 
SELECT 
     replace(street,'Street','St')
   , count(replace(street,'Street','St')) as TotalRepetition
FROM 
   #Address
GROUP BY 
   replace(street,'Street','St')
ORDER BY 
   count(replace(street,'Street','St')) DESC

image

1 Like
3

thanks a lot that really helped!