Merge two select queries sql

multiple-select-query, oracle, sql

Solution

You could do it like this:

WITH TOTAL_MADE
AS
(   
    SELECT SUM(bookings * CategoryPrice )AS Total_Money_Made
    FROM ( SELECT CategoryPrice , count(*) AS bookings
       FROM Booking b
       JOIN performance per
         ON b.performanceid =  per.performanceid
       JOIN Production p
         ON per.productionid = p.productionid          
      WHERE per.performanceid IN (1, 2, 3, 4)
      GROUP BY CategoryPrice)
), TOTAL_CONSESSION
AS
(
    SELECT SUM(ConsessionAmount * 2) AS Total_Consession_Money
      FROM( SELECT COUNT (*) AS ConsessionAmount
    FROM Booking 
    WHERE 
    Consession = 'Yes' AND PerformanceID = '1' OR 
    Consession = 'Yes' AND PerformanceID = '2' OR 
    Consession = 'Yes' AND PerformanceID = '3' OR 
    Consession = 'Yes' AND PerformanceID = '4' )
)
SELECT
    TOTAL_CONSESSION.Total_Consession_Money-
    TOTAL_MADE.Total_Money_Made AS Something
FROM
    TOTAL_MADE,
    TOTAL_CONSESSION;

Problem

Hey i'm using oracle sql to create a database. I im now working on queries and i have come up with two seperate quires which i would like to merge in order to get a result. The first query i have created is to find out the total money made by a performance: ``` SELECT SUM(bookings * CategoryPrice )AS Total_Money_Made FROM ( SELECT CategoryPrice , count(*) AS bookings FROM Booking b JOIN performance per ON b.performanceid = per.performanceid JOIN Production p ON per.productionid = p.productionid WHERE per.performanceid IN (1, 2, 3, 4) GROUP BY CategoryPrice) ``` This gives me a result of: TOTAL_MONEY_MADE ``` 337.5 ``` Then i have another query which works out the total concession money: ``` SELECT SUM(ConsessionAmount * 2) AS Total_Consession_Money FROM( SELECT COUNT (*) AS ConsessionAmount FROM Booking WHERE Consession = 'Yes' AND PerformanceID = '1' OR Consession = 'Yes' AND PerformanceID = '2' OR Consession = 'Yes' AND PerformanceID = '3' OR Consession = 'Yes' AND PerformanceID = '4' ) ``` This gives me result of: TOTAL_CONSESSION_MONEY ``` 18 ``` Now i want a way that i can subtract the result from the second query from the first query. is it possible? and how can i do it? i think it has something to do with sub-queries but im not too sure. Any help? Thanks.

Original source