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?
SQL SUM() with where clause We can selectively find the sum of only those rows, which satisfy the given condition. To do this, we can use the where clause in the SQL statement.
The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. In this syntax: ALL instructs the SUM() function to return the sum of all values including duplicates. ALL is used by default.
Use ORDER BY if you want to order rows according to a value returned by an aggregate function like SUM() . The ORDER BY operator is followed by the aggregate function (in our example, SUM() ).
SUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group. It is better to identify each summary row by including the GROUP BY clause in the query resulst.
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
......
Try this:
SELECT PropertyId, SUM(Amount) as TOTAL_COSTS FROM MyTable WHERE EndDate IS NULL GROUP BY PropertyId
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With