SQL Server：包含 where 子句的多行 SUM()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1607720/
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
SQL Server : SUM() of multiple rows including where clauses
提问by Jimmy
I have a table that looks something like the following :
我有一张类似于以下内容的表：
PropertyID Amount Type EndDate
--------------------------------------------
1 100 RENT null
1 50 WATER null
1 60 ELEC null
1 10 OTHER null
2 70 RENT null
2 10 WATER null
There will be multiple items billed to a property, also billed multiple times. For example RENT could be billed to property #1 12 times (over a year), however the only ones I'm interested for are those with ENDDATE of null (in otherwords, current)
将有多个项目计费到一个属性，也计费多次。例如，RENT 可以向物业 #1 收取 12 次（超过一年），但是我唯一感兴趣的是 ENDDATE 为 null 的那些（换句话说，当前）
I would like to achieve :
我想实现：
PropertyId Amount
--------------------------
1 220
2 80
I have tried to do something like this :
我试图做这样的事情：
SELECT
propertyId,
SUM() as TOTAL_COSTS
FROM
MyTable
However, in the SUM would I be forced to have multiple selects bringing back the current amount for each type of charge? I could see this becoming messy and I'm hoping for a much simpler solution
但是，在 SUM 中，我是否会被迫进行多项选择，以恢复每种类型的收费的当前金额？我可以看到这变得混乱，我希望有一个更简单的解决方案
Any ideas?
有任何想法吗？
回答by Adriaan Stander
This will bring back totals per property and type
这将带回每个属性和类型的总数
SELECT PropertyID,
TYPE,
SUM(Amount)
FROM yourTable
GROUP BY PropertyID,
TYPE
This will bring back only active values
这将只带回活动值
SELECT PropertyID,
TYPE,
SUM(Amount)
FROM yourTable
WHERE EndDate IS NULL
GROUP BY PropertyID,
TYPE
and this will bring back totals for properties
这将带回属性的总数
SELECT PropertyID,
SUM(Amount)
FROM yourTable
WHERE EndDate IS NULL
GROUP BY PropertyID
......
......
回答by gbn
Try this:
尝试这个：
SELECT
PropertyId,
SUM(Amount) as TOTAL_COSTS
FROM
MyTable
WHERE
EndDate IS NULL
GROUP BY
PropertyId
回答by manji
you mean getiing sum(Amount of all types) for each property where EndDate is null:
您的意思是为 EndDate 为 null 的每个属性获取总和（所有类型的数量）：
SELECT propertyId, SUM(Amount) as TOTAL_COSTS
FROM MyTable
WHERE EndDate IS NULL
GROUP BY propertyId
回答by CSharpAtl
sounds like you want something like:
听起来你想要这样的东西：
select PropertyID, SUM(Amount)
from MyTable
Where EndDate is null
Group by PropertyID
回答by Cade Roux
The WHEREclause is always conceptually applied (the execution plan can do what it wants, obviously) prior to the GROUP BY. It must come before the GROUP BYin the query, and acts as a filter beforethings are SUMmed, which is how most of the answers here work.
该WHERE子句始终在概念上应用于GROUP BY. 它必须出现在GROUP BY查询中的之前，并在处理问题之前充当过滤器SUM，这就是此处的大多数答案的工作方式。
You should also be aware of the optional HAVINGclause which must come afterthe GROUP BY. This can be used to filter on the resulting properties of groups after GROUPing - for instance HAVING SUM(Amount) > 0
你也应该知道了可选的HAVING它一定要来子句后的GROUP BY。这可用于在GROUPing后过滤组的结果属性- 例如HAVING SUM(Amount) > 0
回答by Joel
Use a common table expression to add grand total row, top 100is required for order byto work.
使用公共表表达式添加总计行，这top 100是order by工作所必需的。
With Detail as
(
SELECT top 100 propertyId, SUM(Amount) as TOTAL_COSTS
FROM MyTable
WHERE EndDate IS NULL
GROUP BY propertyId
ORDER BY TOTAL_COSTS desc
)
Select * from Detail
Union all
Select ' Total ', sum(TOTAL_COSTS) from Detail
