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
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.
How do I combine these 2 queries to produce something like this QUERY 1 SELECT hotel.country,...
1. For each query, there are one or more things to do. Some of these queries use SQL constructs we haven't covered in class but that you should be able to figure out the meaning of. a. SELECT C.custid, C.companyname FROM Sales.Customers AS C LEFT OUTER JOIN Sales.Orders as O ON C.custid = O.custid WHERE O.orderid IS NULL Provide the result set generated by this query. One of the result rows should be (22, Customer DTDMN). What does this tell...
How can we assess whether a project is a success or a failure? This case presents two phases of a large business transformation project involving the implementation of an ERP system with the aim of creating an integrated company. The case illustrates some of the challenges associated with integration. It also presents the obstacles facing companies that undertake projects involving large information technology projects. Bombardier and Its Environment Joseph-Armand Bombardier was 15 years old when he built his first snowmobile...
A. Issues [1] In addition to damages for one year's notice period, can a trial judge award significant damages for the mere fact of an employee's dismissal, or for the stigma that that dismissal brings? Or for the employer thereafter competing with the ex-employee for the clients, before the ex-employee has got a new job? B. Basic Facts [2] This is an appeal from 2009 ABQB 591 (CanLII), 473 A.R. 254. [3] Usually a judgment recites facts before law. But...
CASE 20 Enron: Not Accounting for the Future* INTRODUCTION Once upon a time, there was a gleaming office tower in Houston, Texas. In front of that gleaming tower was a giant "E" slowly revolving, flashing in the hot Texas sun. But in 2001, the Enron Corporation, which once ranked among the top Fortune 500 companies, would collapse under a mountain of debt that had been concealed through a complex scheme of off-balance-sheet partnerships. Forced to declare bankruptcy, the energy firm...
Read about Cokes strategy in Africa in the article below and discuss the ethics of selling soft drinks to very poor people. Is this an issue that a company like Coke should consider? Africa: Coke's Last Frontier Sales are flat in developed countries. For Coke to keep growing, Africa is it By Duane Stanford Piles of trash are burning outside the Mamakamau Shop in Uthiru, a suburb of Nairobi, Kenya. Sewage trickles by in an open trench. Across the street,...