CODEWITHSUNDEEP
mysqlsql
fatih
fatih

Reputation: 15

Joining 2 sql queries in MySQL

I'm trying to join 2 sql queries in one query.

The first one gets the count of rooms per hotel. The second one gets the count of checked guests in hotel. I'm trying to get occupancy rate per hotel. 

SELECT hotel_id, count(room_id)  
FROM  room  
group by room.hotel_id

SELECT h.hotel_id, count(k.room_id) 
FROM room_reservation as kr , room as k , hotel as h
where kr.room_id = k.room_id and k.hotel_id = h.hotel_id 
group by k.hotel_id

How can i do this ?

Upvotes: 1

Views: 328

Answers (5)

Zane Bien
Zane Bien

Reputation: 23135

It can be done very simply assuming that there will always be equal or less reservations than total hotel rooms at any given time in the room_reservation table and that a hotel room will only have 0 or 1 corresponding rows in the room_reservation table as previous reservations for a room are deleted (it seems that way because in your second query, you are not doing any kind of filtration like selecting only the most recent reservations per room, etc.):

SELECT 
    a.hotel_id, 
    (COUNT(b.room_id) / COUNT(*))*100 AS occupancy_rate  
FROM 
    room a
LEFT JOIN
    room_reservation b ON a.room_id = b.room_id
GROUP BY 
    a.hotel_id

If you need more details about the hotel beyond just the hotel_id, an additional INNER JOIN will be required.

Upvotes: 0

Aprillion
Aprillion

Reputation: 22340

i hope this is self-explanatory:

select hotel_id, sum(guests)/count(room_id) occupancy_level
from (
  select r.hotel_id, r.room_id, count(*) guests
  from room r
  left join room_reservation rr on rr.room_id = r.room_id
  group by r.hotel_id, r.room_id
) temp
group by hotel_id

UPDATE - inspired by @Gordon Linoff to include unreserved rooms:

select r.hotel_id, count(*) / count(distinct r.room_id) occupancy_level
from room r
left join room_reservation rr on rr.room_id = r.room_id
group by r.hotel_id, r.room_id

Upvotes: 0

McGarnagle
McGarnagle

Reputation: 102793

Join all your queries, aggregate to get the number of rooms/reservations per hotel, and divide:

SELECT hotel_id, 
    COUNT(DISTINCT r.room_id) / CONVERT(decimal, COUNT(*)) * 100.0 AS occupancy_rate
FROM hotel h
    LEFT OUTER JOIN room r ON h.hotel_id = r.hotel_id
    LEFT OUTER JOIN room_reservation rr ON r.room_id = rr.room_id
GROUP BY h.hotel_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can definitely do this with one query. One approach is just to union together your queries.

However, I think the following does what you want in one stroke:

SELECT r.hotel_id, count(distinct k.room_id) as numrooms,
       count(distinct kr.room_id) as numreserved
FROM room k left outer join
     room_reservation kr
     on kr.room_id = k.room_id 
group by r.hotel_id

I'm not positive, without knowing more about the tables. In particular, reservations have a time component which rooms and hotels don't have. How is this incorporated into your queries?

Upvotes: 0

aF.
aF.

Reputation: 66757

select aux.hotel_id, ((coalesce(aux2.total, 0)*1.0)/aux.total)*100 as 'ocupancy rate'
from (SELECT hotel_id, count(room_id) as 'total' 
      FROM  room  
      group by room.hotel_id) aux
LEFT OUTER JOIN (SELECT h.hotel_id, COUNT(k.room_id) as 'total'
                 FROM room_reservation as kr
                 INNER JOIN room as k ON (kr.room_id = k.room_id)
                 INNER JOIN hotel as h ON (k.hotel_id = h.hotel_id) 
                 GROUP BY k.hotel_id) aux2 on aux.hotel_id = aux2.hotel_id

Upvotes: 1

Related Questions