SQL Server：多行的SUM（），包括where子句

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?

有任何想法吗？

6 个解决方案

#1

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

......

#2

Try this:

尝试这个：

SELECT
   PropertyId,
   SUM(Amount) as TOTAL_COSTS
FROM
   MyTable
WHERE
   EndDate IS NULL
GROUP BY
   PropertyId

#3

you mean getiing sum(Amount of all types) for each property where EndDate is null:

你的意思是EndDate为null的每个属性的getiing sum（所有类型的数量）：

SELECT propertyId, SUM(Amount) as TOTAL_COSTS
  FROM MyTable
 WHERE EndDate IS NULL
GROUP BY propertyId

#4

sounds like you want something like:

听起来像你想要的东西：

select PropertyID, SUM(Amount)
from MyTable
Where EndDate is null
Group by PropertyID

#5

The WHERE clause is always conceptually applied (the execution plan can do what it wants, obviously) prior to the GROUP BY. It must come before the GROUP BY in the query, and acts as a filter before things are SUMmed, which is how most of the answers here work.

在GROUP BY之前，WHERE子句总是在概念上应用（执行计划可以做它想要的事情）。它必须位于查询中的GROUP BY之前，并且在事物为SUMmed之前充当过滤器，这就是大多数答案的工作方式。

You should also be aware of the optional HAVING clause which must come after the GROUP BY. This can be used to filter on the resulting properties of groups after GROUPing - for instance HAVING SUM(Amount) > 0

您还应该知道必须在GROUP BY之后的可选HAVING子句。这可以用于在GROUPing之后过滤组的结果属性 - 例如HAVING SUM（Amount）> 0

#6

Use a common table expression to add grand total row, top 100 is required for order by to work.

使用公用表表达式添加总计行，按顺序工作需要前100个。

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

#1