Help forming a SQL statement.

Question

I have two tables: attractions and cities. Attractions contains a column called city, which is a reference to the id in the cities table. I want to form a MySQL statement that will show me which cities have the most attractions.

I know I can do:

SELECT COUNT(*) as `number` 
 FROM `attractions` 
WHERE `city` = XX 

...to get a count of how many attractions are in one city, but is there one statement that can count all of the attractions in every city, and return the results in descending order by number of attractions?

Answer 1

SELECT City.Name, COUNT(Attractions.City) as AttractionCount
FROM City
LEFT OUTER JOIN Attractions ON City.City = Attractions.City
GROUP BY City.Name
ORDER BY COUNT(Attractions.City) DESC