如何在INNER JOIN中对SUM的结果使用聚合SUM函数？

This is my SQL query which joins two tables:

这是我的SQL查询,它连接两个表:

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID`

Items are connected to orders via 1-to-many relationship. Each item has a partialAmount column and I am adding up those partial amounts to get a total amount per each order.

物品通过1对多关系连接到订单。每个项目都有一个partialAmount列,我将这些部分金额相加,以获得每个订单的总金额。

I would like to get a total amount for all orders though, how to do that?

我想获得所有订单的总金额,怎么做?

So I would like something like: SUM(amount) AS totalAmount but this doesn't work:

所以我想要像:sum(amount)AS totalAmount但这不起作用:

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`,
       SUM(amount) AS `totalAmount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID`

I don't want to use WITH ROLLUP - because I don't want an extra row.

我不想使用WITH ROLLUP - 因为我不想要额外的行。

Expected output:

-----------------------------------------------------------------------
|   orderID       |        amount      |          totalAmount         |
-----------------------------------------------------------------------
|   1             |          2         |                5             |
-----------------------------------------------------------------------
|   2             |          3         |                5             |
-----------------------------------------------------------------------

4 个解决方案

#1

If all you want is a total line, you can do something like this code. There are other approaches as well, depending on how if you need to filter the selected orders

如果你想要的只是一个总行,你可以做类似这样的代码。还有其他方法,具体取决于您是否需要过滤所选订单

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID` 
UNION
SELECT 999999,sum(i.partialAmount) as 'Amount'
FROM Item i

#2

This will be a hit on performance (and assumes that every row from Item has an order):

这将对性能产生影响(并假设Item中的每一行都有一个订单):

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`,
       (SELECT SUM(partialAmount) FROM `Item`) TotalAmount
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID`

#3

I'd highly advise doing this kind of thing in the application rather than SQL as it is repitition of work, but it can be achieved as follows:

我强烈建议在应用程序中执行此类操作而不是SQL,因为它是工作的重现,但它可以实现如下:

SELECT  OrderTable.OrderID, 
        SUM(PartialAmount) AS Amount,
        TotalAmount
FROM    OrderTable, 
        Item, 
        (SELECT SUM(PartialAmount) AS TotalAmount FROM Item) AS total
WHERE   Item.OrderID = Ordertable.OrderID
GROUP BY OrderTable.OrderID, TotalAmount

I can't check the execution plan to compare this to the option of the Subselect posted in another answer by Lamak, but SQL Server definitely optimises the cross apply much better than the subselect.

我无法检查执行计划,将其与Lamak在另一个答案中发布的Subselect选项进行比较,但SQL Server肯定优化了交叉应用比subselect更好。

#4

If you want the total value on every line you could use sum() over function which would give you both totals http://msdn.microsoft.com/en-us/library/ms189461.aspx

如果你想要每行上的总值你可以使用sum()over函数,这将给你两个总数http://msdn.microsoft.com/en-us/library/ms189461.aspx

for running total

总计

sum(amount) over ()

for partial total

部分总计

sum(amount) over (partition by o.orderid)

would look like this

看起来像这样

SELECT distinct `o`.`orderID`,
       SUM(i.partialAmount) over (partition by orderid) AS `amount`,
       SUM(i.partialAmount) over () AS `totalAmount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
        ON i.orderID = o.orderID

#1