Using COUNT and JOIN in a single query

[Spiff]

My simplified MySQL database contains two databases with data like this

 

cities

idname

1Amsterdam

2Brussels

 

hotels

idnamecity_id

1Hotel A11

2Hotel A21

3Hotel B12

 

In this case, there are 2 hotels in Amsterdam, and 1 in Brussels. This is the information I want to extract with a single query. I use:

 

SELECT cities.name, COUNT(hotels.city_id) AS num FROM cities INNER JOIN hotels ON (cities.id = hotels.city_id) GROUP BY hotels.city_id ORDER BY cities.name

 

I would like to have the result:

 

cities.namenum

Amsterdam2

Brussels1

 

But I get

 

cities.namenum

Amsterdam1

Amsterdam1

Brussels1

 

How do I adjust my query?

[remyaarun]

I tried out this same code with my tables.

And its correctly working.