SQL Sum of Sums

Question

我计算了不同表中计数的总和。此操作执行两次，每个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

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

Answer 1

我永远不会与 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

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

1. 您正在booking和production之间进行笛卡尔连接，这意味着您将每个行数相乘。
2. performance上的子选择将返回一个已知值。根本没有理由这样做。
3. 您正在隐式将数字转换为字符串，然后再转换为数字。
4. 您在这里扫描表或索引 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的预订数量，并将这些预订的乘积相加以为您提供总价值。

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

延伸阅读：

• 加入 (SQL( - 维基百科
• SQL 联接的直观解释 - 编码恐怖
• 功能化 - 网络技术

Answer 2

使用 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)