How do I combine these 2 queries to produce something like this QUERY 1 SELECT h

Question

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

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

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

Explanation / Answer

Query:

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

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.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.