CODEWITHSUNDEEP
mysqlselectcount
user3733831
user3733831

Reputation: 2926

How I use mysql count with select query?

I have a mysql select query like this: 

select r.restaurant_id, r.restaurant_name, r.city_id, c.name
from restaurants r
inner join cities c on c.id = r.city_id;

This is the result of above query: 

+---------------+----------------------+---------+-------------+
| restaurant_id | restaurant_name      | city_id | name        |
+---------------+----------------------+---------+-------------+
|             7 | Somasiri Bake House  |       5 | Mumbai      |
|             8 | Indian    Bake House |       7 | Chennai     |
|             9 | KFC Rest             |       5 | Mumbai      |    
|            10 | Indian t             |       5 | Mumbai      |
+---------------+----------------------+---------+-------------+

Now I want to display all the available cities with the number of restaurants existing to one city.

Eg: Mumbai (3), Chennai(1) and so on

I tried it like below with mysql COUN(), but it doesn't work for me. 

SELECT c.name, count(r.city_id) AS count
FROM cities c
INNER JOIN restaurants r ON c.id = r.city_id;

Can anybody tell me what is the wrong with this?

Hope somebody may help me out. Thank you.

Upvotes: 0

Views: 183

Answers (3)

MPelletier
MPelletier

Reputation: 16677

That's called a grouping or aggregate query, you need to tell it how to group your elements.

Just add

GROUP BY r.restaurant_id, r.restaurant_name, r.city_id, c.name

at the end, before your final semi-colon.

Upvotes: 1

M0rtiis
M0rtiis

Reputation: 3774

SELECT c.name, COALESCE(count(r.city_id), 0) AS count
FROM cities c
LEFT JOIN restaurants r ON c.id = r.city_id
GROUP BY c.id

Upvotes: 1

Bohemian
Bohemian

Reputation: 424993

Use a simple group by if you don't want restaurant data:

select c.name, count(r.city_id) as available
from cities c
left join restaurants r on c.id = r.city_id
group by r.city_id

See SQLFiddle.

Or, if you want restaurant data too, select from cities first, then left join to other tables so cities without restaurants still get returned. Add a left join to a subquery that calculates each city's frequency:

select
  r.restaurant_id,
  r.restaurant_name,
  c.id,
  c.name,
  coalesce(available, 0) available
from cities c
left join restaurants r on c.id = r.city_id
left join (select city_id, count(*) available from restaurants group by 1) a
  on a.city_id = r.city_id

See SQLFiddle.

Upvotes: 1

Related Questions