How I use mysql count with select query?

Question

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.

stackoverflow.com/a/4728175/2625561

klenium
– klenium

2015-07-25 15:00:16 +00:00
Commented Jul 25, 2015 at 15:00 — klenium
– klenium, Commented Jul 25, 2015 at 15:00
You are missing a group by.

Gordon Linoff
– Gordon Linoff

2015-07-25 15:01:51 +00:00
Commented Jul 25, 2015 at 15:01 — Gordon Linoff
– Gordon Linoff, Commented Jul 25, 2015 at 15:01

M0rtiis · Accepted Answer · 2015-07-25 15:52:42Z

1

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

edited Jul 25, 2015 at 15:52

answered Jul 25, 2015 at 15:36

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3733831 Over a year ago

Can not get all available cities.

user3733831 Over a year ago

Yes.. This is what I needed. Thank you very much

Bohemian · Accepted Answer · 2015-07-25 15:51:27Z

1

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.

edited Jul 25, 2015 at 15:51

answered Jul 25, 2015 at 15:03

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

5 Comments

user3733831 Over a year ago

Bohemian, your query returned all the available restaurants with city counts. But I need to display all the cities and if there are restaurant for these cities then I need to display count number along with city.

Bohemian Over a year ago

@user3733831 do you want a zero in the "available" column for each city that has no restaurants in it, and to display nulls for the restaurant details in that case?

user3733831 Over a year ago

Yes it may have null values for cities. Reason is there are no restaurants for every cities and I don't need to have duplicate of cities in the query. Thank you.

Bohemian Over a year ago

@user3733831 try the new version (sqlfiddle link has been updated too)

user3733831 Over a year ago

Yes dear.. your query is working. But city names are duplicating. Actually I need like city1 (3), city2 (5), city3 (8), city4 (11), city5

MPelletier · Accepted Answer · 2015-07-26 04:26:56Z

1

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.

edited Jul 26, 2015 at 4:26

answered Jul 25, 2015 at 14:59

MPelletier

16.8k18 gold badges90 silver badges140 bronze badges

5 Comments

Michael Berkowski Over a year ago

I'd encourage the correct use of GROUP BY (not MySQL's incorrect behavior) by adding all the SELECT columns to the group: GROUP BY r.restaurant_id, r.restaurant_name, r.city_id, c.name Using GROUP BY r.city_id alone would be an error in almost any other database.

user3733831 Over a year ago

yes its working... but it doesnt select all the cities

Gordon Linoff Over a year ago

@MichaelBerkowski . . . I agree with you in general. However, this particular example is not incorrect behavior according to ANSI, because of the notion of functional dependence. I think Postgres, which may be the only database that implements this functionality correctly, does an adequate job of explaining the notion (postgresql.org/docs/9.4/static/sql-select.html).

user3733831 Over a year ago

Can we select all available cities even if there is no any restaurant

MPelletier Over a year ago

I agree with @MichaelBerkowski, grouping by all un-aggregated columns is ideal.

Collectives™ on Stack Overflow

How I use mysql count with select query?

3 Answers 3

2 Comments

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related