oracle SQL 总和

SQL Sum of Sums

提问by Davide Sousa

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

我已经计算了不同表中计数的总和。这样做两次，每个performanceID. 现在我想得到两个总和的总和。

Below is the code for the two sums that I do at the moment:

以下是我目前所做的两个总和的代码：

    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 results I get are:

我得到的结果是：

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

How do I sum up the two sums here?

我如何总结这里的两个总和？

回答by Ben

I'm never going to compete with the FGITW but I have to say something about this query...

我永远不会与 FGITW 竞争，但我必须对这个查询说些什么......

If we add whitespace I hope you'll 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

Breaking the query down the onlyreason that you got two rows returned were the analytic functions and the union all.

将查询分解为返回两行的唯一原因是分析函数和 union all。

1. You're doing a cartesian joinbetween bookingand production, this means that you multiply the number of rows in each by each other.
2. Your sub-selects on performanceare returning one value, which is already known. There's no reason to do them at all.
3. You're implicitly converting numbers into strings and back into numbers again.
4. You're scanning a table or index 8 times here!

1. 您正在和之间进行笛卡尔连接，这意味着您将每个中的行数相乘。bookingproduction
2. 您的子选择performance返回一个已知值。根本没有理由这样做。
3. 您隐式地将数字转换为字符串并再次转换回数字。
4. 您正在此处扫描表或索引 8 次！

It appears as though you want the total amount taken for each performance in which case your query can be simplified to the following:

看起来好像您想要为每次性能采取的总金额，在这种情况下，您的查询可以简化为以下内容：

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
                )

Please note the explicit join syntax, this has been around for a few decades, makes things a lot clearer and helps stop mistakes. This query will do two range scans, one of bookingand one of production, assuming you have indexes on performanceidon both tables. It'll also do a unique scan of performanceassuming that performanceidis the primary key of this table.

请注意显式连接语法，这已经存在了几十年，使事情变得更加清晰并有助于防止错误。假设您在两个表上都有索引，此查询将执行两次范围扫描，一次booking和一次。它还会做一个独特的扫描，假设它是这个表的主键。productionperformanceidperformanceperformanceid

As an explanation of what this does, now I've finally managed to get your schema correct! We select the two performances, 1and 2. We then select every production related to those performances and every booking related to those productions. You may be able to simplify this further depending on what table categorypriceis in. We then get the number of bookings per categorypriceand sum the product of these to give you the total value.

作为对它的作用的解释，现在我终于设法使您的架构正确无误！我们选择两个表演，1和2。然后我们选择与这些表演相关的每个作品以及与这些作品相关的每个预订。您可以根据所在的桌子进一步简化此操作categoryprice。然后我们获取每个预订的数量categoryprice并将这些乘积相加得出总价值。

As a bit of advice, I would alwaysrecommend understanding what values you expect to be returned from a query beforeyou accept that your query is correct. The very best can and do make mistakes. Being able to catch them because you can see that the returned values are incorrect will help.

作为一点建议，我始终建议您在接受查询正确之前了解您希望从查询中返回哪些值。最好的人可以而且确实会犯错误。能够捕获它们，因为您可以看到返回的值不正确将有所帮助。

Further Reading:

进一步阅读：

• Join (SQL)- Wikipedia
• A Visual Explanation of SQL Joins- Coding Horror
• In Function- Tech on the Net

• 加入 (SQL)- 维基百科
• SQL 连接的可视化解释- 编码恐怖
• 在功能上- 网络技术

回答by hkutluay

Use sub sql, and sum TotalAmount

使用 sub sql，并总结 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)