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

Question

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?

Answer 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 

......

Answer 2

Try this:

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