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

Viewed 219234

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?

6 Answers
Related