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 ?
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
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?
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
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
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.
number of guests/all rooms,number of guests/occupied roomsornumber of occupied rooms/all rooms?