SQL Server : SUM() of multiple rows including where clauses
I have a table that looks something like the following :
PropertyID Amount Type EndDate
--------------开发者_如何学Go------------------------------
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?
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
you mean getiing sum(Amount of all types) for each property where EndDate is null:
SELECT propertyId, SUM(Amount) as TOTAL_COSTS
FROM MyTable
WHERE EndDate IS NULL
GROUP BY propertyId
sounds like you want something like:
select PropertyID, SUM(Amount)
from MyTable
Where EndDate is null
Group by PropertyID
The WHERE
clause is always conceptually applied (the execution plan can do what it wants, obviously) prior to the GROUP BY
. It must come before the GROUP BY
in the query, and acts as a filter before things are SUM
med, which is how most of the answers here work.
You should also be aware of the optional HAVING
clause which must come after the GROUP BY
. This can be used to filter on the resulting properties of groups after GROUP
ing - for instance HAVING SUM(Amount) > 0
Use a common table expression to add grand total row, top 100
is required for order by
to work.
With Detail as
(
SELECT top 100 propertyId, SUM(Amount) as TOTAL_COSTS
FROM MyTable
WHERE EndDate IS NULL
GROUP BY propertyId
ORDER BY TOTAL_COSTS desc
)
Select * from Detail
Union all
Select ' Total ', sum(TOTAL_COSTS) from Detail
精彩评论