How do I get count of events for each city

events table has a column called "city_id" . I would like to see the result like this

Miami 30
Tampa开发者_开发百科 11
San Franciso 29

I am using mysql.

INNER JOIN, GROUP BY, COUNT... That's most likely all you need to make this simple query!

Something like this will help you

SELECT 
  city_id,
  max(city_name),
  count(*)
FROM events
GROUP BY
  city_id

Assuming you had city name in another table

select e.city_id, c.name, count(*) eventcount
from events e
join city c on c.id = e.city_id
group by e.city_id, c.name