SQL -如何根据单个行计算对数据进行分组

I'm fascinated by this problem. Here's a stripped down version of my full sqlite query:

我对这个问题很感兴趣。下面是我完整的sqlite查询的简化版本:

SELECT OrderID,
       UnitPrice,
       Quantity,
       Discount
FROM Order Details;

Which returns this:

OrderID  UnitPrice  Quantity  Discount
10248    14.0       12        0.00
10248    9.8        10        0.00
10248    34.8        5        0.00
10249    18.6        9        0.00
10249    42.4       40        0.00
10250    7.7        10        0.00
10250    42.4       35        0.15
10250    16.8       15        0.15
10251    16.8       6         0.05
10251    15.6       15        0.05

I want to return a new column called total price. This would be a calculation of UnitPrice multiplied by Quantity less the corresponding percentage Discount. The complication arises however in that the new column should be grouped by the OrderID to represent the total order price less the individual % discounts.

我想返回一个名为total price的新列。这将是计算单价乘以数量减去相应的百分比折扣。然而，复杂的是，新的列应该按照OrderID分组，以表示总的订单价格，而不是单个的%折扣。

I've been chasing my tail with SUM aggregates and GROUP BY's for hours. Summing the UnitPrices and Quantities is fine, but as the discount applies to different values it's almost like the discount needs to be calculated first. The discount does not need to apply individually to each item btw.

几个小时以来，我一直在用总数和组群来追我的尾巴。将单价和数量相加是可以的，但是由于折扣适用于不同的值，所以折扣几乎需要首先计算。顺便说一下，折扣不需要单独适用于每个项目。

1 个解决方案

#1

This is assuming you use MySQL. Slight variation may be needed for MS SQL server but the idea is the same for both.

这是假设你使用MySQL。对于MS SQL server来说，可能需要细微的变化，但是对于这两种方法来说都是一样的。

For sake of understanding this better, split this into two stages.

为了更好地理解这一点，将其分为两个阶段。

First count the line item price:

第一次数线项价格:

SELECT OrderID,
       UnitPrice,
       Quantity,
       Discount,
       UnitPrice*Quantity*(1-Discount) total
      FROM OrderDetails;

And then apply GROUP BY with SUM(), so your final query would be:

然后用SUM()对GROUP BY应用，最终查询为:

SELECT OrderID,
       SUM(UnitPrice*Quantity*(1-Discount)) total
      FROM OrderDetails
      GROUP BY OrderID;

#1