MySQL Join Query GROUP BY

Question

I have 2 tables. (hotels, locations).

I want to retrieve all the locations with a count of number of Hotels in that location.

Right now I have the following query:

SELECT locations.*, 
COUNT(*) as no_of_hotels 
FROM locations RIGHT JOIN hotels ON hotels.location_id = locations.id 
GROUP BY locations.id 
ORDER BY no_of_hotels DESC

The query works fine, but the problem is, It gives me only the locations with 1 or more hotels. I want to show all the locations (even though the location has no hotels).

Lajos Veres · Accepted Answer · 2014-04-12 10:03:18Z

2

You should change to left join:

SELECT locations.*, 
       COUNT(distinct hotels.id) as no_of_hotels 
       FROM locations 
       LEFT JOIN hotels ON hotels.location_id = locations.id 
       GROUP BY locations.id 
       ORDER BY no_of_hotels DESC

Otherwise probably this is easier to understand:

select
  locations.*
  (select count(*) from hotels where hotels.location_id = locations.id) as no_of_hotels 
from
  locations
order by no_of_hotels desc

edited Apr 12, 2014 at 10:03

answered Apr 12, 2014 at 9:54

Lajos Veres

13.7k7 gold badges47 silver badges57 bronze badges

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

2 Comments

Divine Propotion Over a year ago

Left join works, but it gives the value 1 for no_of_hotels, even though there aren't any hotels. But the second solution works well.

Lajos Veres Over a year ago

I modified a bit. Can you try it? (And probably the second one would give the correct result as well.

haris · Accepted Answer · 2014-04-12 10:07:24Z

1

SELECT locations.*, (SELECT count(*) FROM hotels WHERE hotels.location_id = locations.id) AS no_of_hotels FROM locations ORDER BY no_of_hotels DESC

answered Apr 12, 2014 at 10:07

haris

3,8751 gold badge27 silver badges28 bronze badges

Comments

Sadikhasan · Accepted Answer · 2014-04-12 09:59:11Z

0

Try with INNER JOIN like this

SELECT locations.*, 
COUNT(*) as no_of_hotels 
FROM locations INNER JOIN hotels ON hotels.location_id = locations.id 
GROUP BY locations.id 
ORDER BY no_of_hotels DESC

answered Apr 12, 2014 at 9:59

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Collectives™ on Stack Overflow

MySQL Join Query GROUP BY

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related