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

User · Answer

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

User · Answer

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 SUMmed  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 GROUPing - for instance HAVING SUM Amount   gt  0

User · Answer

Try this   SELECT    PropertyId     SUM Amount  as TOTAL COSTS FROM    MyTable WHERE    EndDate IS NULL GROUP BY    PropertyId

User · Answer

sounds like you want something like   select PropertyID  SUM Amount  from MyTable Where EndDate is null Group by PropertyID

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to aggregate