SQL Group By 查询中的条件总和 MSSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16517298/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional sum in Group By query MSSQL
提问by FreddieGericke
I have a table OrderDetails with the following schema:
我有一个具有以下架构的表 OrderDetails：
----------------------------------------------------------------
| OrderId | CopyCost | FullPrice | Price | PriceType |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------
| 16 | 50 | 100 | 100 | FullPrice |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------
| 16 | 50 | 100 | 50 | CopyCost |
----------------------------------------------------------------
I need a query that will surmise the above table into a new table with the following schema:
我需要一个查询，将上表推测为具有以下架构的新表：
----------------------------------------------------------------
| OrderId | ItemCount | TotalCopyCost | TotalFullPrice |
----------------------------------------------------------------
| 16 | 4 | 150 | 100 |
----------------------------------------------------------------
Currently I am using a Group By on the Order.Id to the the item count. But I do not know how to conditionally surmise the CopyCost and FullPrice values.
目前我在 Order.Id 上使用 Group By 来计算项目数。但我不知道如何有条件地推测 CopyCost 和 FullPrice 值。
Any help would be much appreciated.
任何帮助将非常感激。
Regards Freddie
问候房地美
回答by peterm
回答by Meherzad
Try this query
试试这个查询
select
orderId,
count(*) as cnt,
sum(if(pricetype='CopyCost', CopyCost, 0)) as totalCopyCost,
sum(if(pricetype='FullPrice', FullPrice, 0)) as totalFullPrice
from
tbl
group by
orderId
SQL FIDDLE:
SQL小提琴：
| ORDERID | CNT | TOTALCOPYCOST | TOTALFULLPRICE |
--------------------------------------------------
| 16 | 4 | 150 | 100 |
回答by Matt Mitchell
Could you use:
你能不能用：
SELECT
OrderId,
Count(1) as ItemCount,
SUM(CASE WHEN PriceType = 'CopyCost'
THEN CopyCost ELSE 0 END) AS TotalCopyCost,
SUM(CASE WHEN PriceType = 'FullPrice'
THEN FullPrice ELSE 0 END) AS TotalFullPrice
FROM OrderDetails
GROUP BY OrderId
回答by Verma
You could also try...
你也可以试试...
select A.OrderID, A.ItemCount,B.TotalCopyCost, C.TotalFullPrice
from (select OrderID, count(*) as ItemCount from orderdetails) as A,
(select OrderID, sum(CopyCost) as TotalCopyCost from orderdetails where PriceType = 'CopyCost') as B,
(select OrderID, sum(FullPrice) as TotalFullPrice from orderdetails where PriceType = 'FullPrice') as C
where A.OrderID = B.OrderID
SQLFiddle: http://sqlfiddle.com/#!2/946af/6
SQLFiddle：http://sqlfiddle.com/#!2/946af/6
