Help writing a mysql query

I'm getting a headache from this query, I have no idea how to solve it. Perhaps I should just do the math in PHP but I can't seem to do that either.. It's BRAINBREAKING.

I need data from combining two tables:

event_hotel_reservation:

an开发者_如何学运维d hotel_room_type:

I want to write a query which shows me the total beds per day per hotel. The total beds can be found by doing totalGuests * capacity.

I then want to order them by day, so I know how many beds I need to book per day, per hotel.

This is the query I tried, but failed miserably:

select ehr.day, (ehr.totalguests * hrt.capacity) as total 
from event_hotel_reservation ehr 
inner join hotel_room_type hrt on ehr.roomtypeid = hrt.roomtypeid 
group by day

Any help? thanks in advance.

You are close, but you have to also group by hotel. Try:

    SELECT ehr.day, (ehr.totalguests * hrt.capacity) AS total 
      FROM event_hotel_reservation ehr 
INNER JOIN hotel_room_type hrt 
        ON ehr.roomtypeid = hrt.roomtypeid 
  GROUP BY ehr.hotelID, ehr.day

I hope I interpreted your schema correctly

select ehr.hotelid, ehr.day, 
sum(hrt.capacity) as total_capacity, 
sum(ehr.totalguests) as total_used_capacity, 
sum(hrt.capacity - ehr.totalguests) as total_free_capacity
from event_hotel_reservation ehr 
inner join hotel_room_type hrt on ehr.roomtypeid = hrt.roomtypeid 
group by ehr.hotelid, ehr.day