Geek asks and answers

Sum of SQL Amounts

I calculated the invoice amount in different tables. This is done twice, once for each performanceID. Now I want to get the sum of two sums.

Below is the code for the two amounts that I am currently doing:

    SELECT SUM((COUNT (BookingID) * CategoryPrice)) AS TotalAmount
    FROM Booking, Production
    WHERE Booking.PerformanceID IN(SELECT PerformanceID FROM Performance WHERE PerformanceID = '1')
    and Production.ProductionID IN 
    (SELECT ProductionID FROM Performance WHERE PerformanceID = '1') 
    GROUP BY BookingID, CategoryPrice
    UNION ALL
    SELECT SUM((COUNT (BookingID) * CategoryPrice)) AS TotalAmount
    FROM Booking, Production
    WHERE Booking.PerformanceID IN(SELECT PerformanceID FROM Performance WHERE PerformanceID = '2')
    and Production.ProductionID IN 
    (SELECT ProductionID FROM Performance WHERE PerformanceID = '2')
     GROUP BY BookingID, CategoryPrice

The received results:

TOTALAMOUNT
-----------
         70
         60

How can I sum two sums here?

+3
source share
2 answers

I will never compete with FGITW, but I have to say something about this request ...

If we add spaces, I hope you see what I mean:

SELECT SUM( (COUNT(BookingID) * CategoryPrice) ) AS TotalAmount
  FROM Booking
     , Production
 WHERE Booking.PerformanceID IN ( SELECT PerformanceID 
                                   FROM Performance 
                                  WHERE PerformanceID = '1')
   AND Production.ProductionID IN ( SELECT ProductionID FROM Performance 
                                     WHERE PerformanceID = '1') 
 GROUP BY BookingID, CategoryPrice
 UNION ALL
SELECT SUM( (COUNT(BookingID) * CategoryPrice)) AS TotalAmount
  FROM Booking
     , Production
 WHERE Booking.PerformanceID IN ( SELECT PerformanceID 
                                    FROM Performance 
                                   WHERE PerformanceID = '2')
   AND Production.ProductionID IN ( SELECT ProductionID 
                                      FROM Performance 
                                     WHERE PerformanceID = '2')
 GROUP BY BookingID, CategoryPrice

Interrupting the request down, the only reason you got two lines is analytic functions and the union of all.

  • booking production, , .
  • performance , . .
  • .
  • 8 !

, , :

SELECT SUM(bookings * CategoryPrice)
  FROM ( SELECT CategoryPrice , count(*) as bookings
           FROM Booking b
           JOIN performance per
             ON p.performanceid =  per.performanceid
           JOIN Production p
             ON p.productionid = per.productionid
          WHERE p.performanceid in (1, 2)
          GROUP BY CategoryPrice
                )

, , , . , booking production, performanceid . performance, , performanceid .

, , ! : 1 2. , , , . , , categoryprice. categoryprice , .

, , , , . . , , , .

:

+7

sub sql sum TotalAmount

SELECT SUM(TotalAmount)
   ( SELECT SUM((COUNT (BookingID) * CategoryPrice)) AS TotalAmount
        FROM Booking, Production
        WHERE Booking.PerformanceID IN(SELECT PerformanceID FROM Performance WHERE PerformanceID = '1')
        and Production.ProductionID IN 
        (SELECT ProductionID FROM Performance WHERE PerformanceID = '1') 
        GROUP BY BookingID, CategoryPrice
        UNION ALL
        SELECT SUM((COUNT (BookingID) * CategoryPrice)) AS TotalAmount
        FROM Booking, Production
        WHERE Booking.PerformanceID IN(SELECT PerformanceID FROM Performance WHERE PerformanceID = '2')
        and Production.ProductionID IN 
        (SELECT ProductionID FROM Performance WHERE PerformanceID = '2')
Group By CategoryPrice)
0

More articles:


All Articles