Geek asks and answers

Merge two select sql queries

Hi, I am using oracle sql to create a database.

Now I am working on queries, and I have two separate queries that I would like to combine to get the result.

The first query I created is to find out the total amount of money produced by the 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 the result:

TOTAL_MONEY_MADE

       337.5

Then I have another request that returns the total amount of 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 the result:

TOTAL_CONSESSION_MONEY

                18

Now I want me to be able to subtract the result from the second query from the first query. Is it possible? and how can i do this? I think this has something to do with subqueries, but I'm not sure.

Any help? Thank.

+3
source share
3 answers

:

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;
+5

select ,

select (put your second query here) - (put your first select here) from dual
+1

- :

SELECT Total_Money_Made - Total_Consession_Money FROM

( SELECT SUM(bookings * CategoryPrice )AS Total_Money_Made, ( 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' )
    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) )
0

More articles:


All Articles