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
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
GROUP BY street
ORDER BY TotalRepeat DESC