MySQL Distinct field with a count

If you play with databases, I’m sure you’ve run across this problem before. Let’s say you have a bunch of addresses and you want a list of all the cities in your database. Then let’s say you want a count of all the addresses in each city. This is trivial SQL but I always seem to forget it. So, if your field name is ‘city’ and your table name is ‘addresses’, here is your SQL.

SELECT city, count(id) AS cnt FROM
addresses WHERE 1=1 GROUP BY city HAVING
cnt > 1 ORDER BY cnt DESC

Leave a Reply

Your email address will not be published.