SQL求和总和

Question

SQL求和总和

3

我已经计算出不同表格中计数的总和。这是两次完成的，每次都针对performanceID。现在我想得到这两个总和的总和。

下面是我目前执行这两个总和的代码：

    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

我得到的结果是:

总金额
-----------
         70
         60

这里如何将两个总数相加呢？

- Davide Sousa

2个回答

0

使用子查询，对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)

- hkutluay

这将会给我一个错误，说它需要一个分组函数。 - Davide Sousa

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ben · Accepted Answer

我永远不会与FGITW竞争，但我必须对这个查询发表一些看法...

如果我们添加空格，我希望你能明白我的意思：

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

将查询分解，导致返回两行的唯一原因是分析函数和union 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
                )

请注意明确的连接语法，这已经存在了几十年，使事情更清晰，并有助于防止错误。假设您在两个表的performanceid上都有索引，此查询将执行两个范围扫描，一个是booking，另一个是production。它还将执行performance的唯一扫描，假设performanceid是此表的主键。

作为对这个操作的解释，现在我终于成功地理解了您的模式！我们选择了1和2这两个演出。然后选择与这些演出相关的每个制片以及与这些制片相关的每个预订。您可以根据categoryprice表所在的位置进一步简化此操作。然后获取每个categoryprice的预订数量，并对这些数量求和以给出总价值。

作为建议，我会始终建议在接受查询正确之前了解您期望从查询中返回的值。即使是最好的人也可能犯错。能够捕捉它们，因为您可以看到返回的值是不正确的，这将有所帮助。

进一步阅读：

Join (SQL) - 维基百科
SQL Join的可视化解释 - Coding Horror
In Function - Tech on the Net