Question

How do I combine these 2 queries to produce something like this

country year month checkedout booked Total (checkout+booked Afghanistan 2007 Afghanistan 2014 Afghanistan 2014 Albania 2010 Angola 2001 Argentina 2003 6 2 7

QUERY 1

SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked

FROM booking

LEFT JOIN room on room.room_id = booking.room_id

LEFT JOIN hotel on room.hotel_id = hotel.hotel_id

LEFT JOIN time on booking.time_id = time.time_id

GROUP BY hotel.country, time.year, time.month

ORDER by hotel.country, time.year, time.month

QUERY 2

SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout

FROM checkout

LEFT JOIN room on room.room_id = checkout.room_id

LEFT JOIN hotel on room.hotel_id = hotel.hotel_id

LEFT JOIN time on checkout.time_id = time.time_id

GROUP BY hotel.country, time.year, time.month

ORDER BY hotel.country, time.year, time.month

Answer 1

Query:

SELECT T1.country, T1.year, T1.month ,T1.booked,T2.checkedout, (T1.booked T2.checkedout) AS 'Total (booked + checkout)'

FROM 
(
SELECT hotel.country, time.year, time.month, COUNT(booking.room_id) as booked 

FROM booking 
LEFT JOIN room on room.room_id = booking.room_id 

LEFT JOIN hotel on room.hotel_id = hotel.hotel_id 

LEFT JOIN time on booking.time_id = time.time_id 

GROUP BY hotel.country, time.year, time.month 

ORDER by hotel.country, time.year, time.month
) AS T1
INNER JOIN
(
SELECT hotel.country, time.year, time.month, COUNT(checkout.room_id) as checkedout

FROM checkout

LEFT JOIN room on room.room_id = checkout.room_id

LEFT JOIN hotel on room.hotel_id = hotel.hotel_id

LEFT JOIN time on checkout.time_id = time.time_id

GROUP BY hotel.country, time.year, time.month

ORDER BY hotel.country, time.year, time.month
)AS T2
ON T1.country=T2.country
AND T1.year=T2.year
AND T1.month=T2.month

I simply combined both the queries. I created temporary views named T1 and T2 and added a join used that.

Please read the highlighted part which are the main modification of the code.