SQL Server : SUM() of multiple rows including where clauses

aggregate, sql, sql-server, t-sql

Solution

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

......

Problem

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) 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 Any ideas?

Original source