TK Bruin TK Bruin - 1 year ago 59
SQL Question

How To Get Running Subtotal with Group By in SQL Server

How can I get a running sub-total of amounts for a group in SQL 2014?

I have a table with transaction amounts. I need to summarize to get a row for each project and quarter for which there is data, and need a running subtotal within each project. The running total would need to reset to zero for each new project.

Here is what I have so far:

SELECT [ProjectId]
, SUM( ActualAmount) AS PeriodAmount
, SUM( ActualAmount) OVER (PARTITION BY ProjectId ORDER BY ProjectId,YearQuarter)
AS FairMarketValue

FROM GLSnapshot
GROUP BY [ProjectId] , [YearQuarter]

I currently get this error:

Msg 8120, Level 16, State 1, Line 3

Column 'GLSnapshot.ActualAmount' is invalid in the
select list because it is not contained in either an
aggregate function or the GROUP BY clause.

Sample Data: Assuming I have the following data for table GLSnapshot:

ProjectId, YearQuarter, ActualAmount
'A', '2015Q1' , 9000.00
'A', '2015Q1' , 100.00
'A', '2015Q2' , 50.00
'A', '2015Q3' , 50.00
'A', '2015Q3' , 200.00
'B', '2015Q1' ,80000.00

I should get the following result for

ProjectId, YearQuarter, PeriodAmount, FairMarketValue (Running Subtotal):
'A', '2015Q1' , 9100.00 , 9100.00
'A', '2015Q2' , 50.00 , 9150.00
'A', '2015Q3' , 250.00 , 9400.00
'B', '2015Q1' ,80000.00 , 80000.00

Answer Source

OLAP functions are calculated after aggregation, you can't use ActualAmount, must be SUM( ActualAmount). And there's no need to order by ProjectId because it's already in PARTITION BY. Finally use ROWS UNBOUNDED PRECEDING otherwise it defaults to RANGE UNBOUNDED PRECEDING which is more expensive and might not return the expected result:

SELECT [ProjectId]
    , [YearQuarter]
    , SUM( ActualAmount) AS PeriodAmount
    , SUM( SUM( ActualAmount))
      OVER (PARTITION BY ProjectId
            ORDER BY YearQuarter
            ROWS UNBOUNDED PRECEDING) AS FairMarketValue

    FROM GLSnapshot 
    GROUP BY [ProjectId] , [YearQuarter]