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?