SQL Count and group duplicates

Question

Please advise the best way for solving my problem.

I have a problem figuring out how to count duplicates in table like below

Street       | City

avenue 123   | New York
avenue 123   | New York
avenue 20    | New York
avenue 35    | Chicago
avenue 12    | Chicago
avenue 123   | Chicago
avenue 12    | Chicago
avenue 12    | Chicago

I would like to have number of duplicate streets in the same City as result below?

result:

Street     | City | Duplicates

avenue 123 | New York | 2
avenue 12  | Chicago | 3

Answer 1

Use GROUP BY, COUNT and HAVING:

 SELECT Street, City, COUNT(*)
 FROM yourtable
 GROUP BY Street, City
 HAVING COUNT(*) > 1

See it working online: sqlfiddle

Answer 2

Try :

SELECT street, city, COUNT(*) AS duplicates
FROM yourtable
GROUP BY street, city
HAVING COUNT(*) >1

Remove HAVING COUNT(*) > 1 if you want to display lines without duplicates as well.