Running total for grouped data

Question

Running total for grouped data

Peter Bishop 256

Given input data similar to:

Month	Region	Store	Sales
Jan		R1		S1		10.00
Jan		R1		S1		15.00
Jan		R1		S1		30.00
Jan		R1		S2		 5.00
Jan		R1		S2		 1.00
Jan		R2		S3		17.00
Jan		R2		S3		 5.00
Jan		R2		S4		60.00
Jan		R2		S4		45.00
Jan		R2		S4		11.00
Feb		R1		S1		20.00
Feb		R1		S1		40.00
Feb		R1		S1		60.00
Feb		R2		S3		10.00
Feb		R2		S3		10.00
Feb		R2		S3		10.00
Feb		R2		S3		10.00

I'd like output of the form:

Month	Region	Store	 Sales
Jan		R1		S1		 55.00	 -- 10+15+30
Jan		R1		S2		  6.00	 -- 5+1
Jan		R2		S3		 22.00	 -- 17+5
Jan		R2		S4		116.00	 -- 60+45+11
Feb		R1		S1		175.00	 -- 20+40+60 + 55 (Jan's total)
Feb		R1		S2		  6.00	 -- (Jan's Total)
Feb		R2		S3		 62.00	 -- 10+10+10+10 + 22 (Jan's Total)
Feb		R2		S4		116.00	 -- (Jan's Total)
Mar		R1		S1		???.00	 -- (Mar's sales) + (Feb's total)
Mar		R1		S2		???.00	 -- (Mar's sales) + (Feb's total)
Mar		R2		S3		???.00	 -- (Mar's sales) + (Feb's total)
Mar		R2		S4		???.00	 -- (Mar's sales) + (Feb's total)
etc...

I can get a SUM() / GROUP BY to break down the month, region, store values for each combination but it won't do the ROLLUP.

I've also tried:

SUM([Sales]) OVER(PARTITION BY [Region],[Store] ORDER BY [Month] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

but this gave me a running total for every line rather than grouped by month, region, store. For example:

Month	Region	Store	Sales
Jan		R1		S1		10.00	 -- 10
Jan		R1		S1		25.00	 -- 10+15
Jan		R1		S1		50.00	 -- 10+15+30
Jan		R1		S2		 5.00	 -- 5
Jan		R1		S2		 6.00	 -- 5+1
etc...

Is there a way to achieve this without multiple queries (e.g. SUM() GROUP BY --> "ROWS BETWEEN".

Thanks.

0 comments

2 answers

Your answer

Answer 1

Viorel 127K

You could also try a longer query:

;
with Q0 as
(
	select *, charindex([Month], 'JanFebMarAprMayJunJulAugSepOctNovDec') as MonthIndex
	from MyTable
),
Q1 as
(
	select distinct Region, Store
	from Q0
),
Q2 as
(
	select distinct [Month], MonthIndex, Q1.Region, Q1.Store
	from Q0, Q1
)
select [Month], Region, Store, (select sum(Sales) from Q0 where Region=Q2.Region and Store=Q2.Store and MonthIndex<=Q2.MonthIndex) as Sales
from Q2
order by MonthIndex

It would be simpler if the month were numeric.

0 comments

Answer 2

Use a two-step aggregation: first aggregate to Month/Region/Store, then apply the window function over that aggregated result.

Example:

;WITH MonthlyStoreSales AS
(
    SELECT
        [Month],
        [Region],
        [Store],
        SUM([Sales]) AS MonthlySales
    FROM dbo.YourTable
    GROUP BY [Month], [Region], [Store]
)
SELECT
    [Month],
    [Region],
    [Store],
    SUM(MonthlySales) OVER
    (
        PARTITION BY [Region], [Store]
        ORDER BY [Month]
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Sales
FROM MonthlyStoreSales
ORDER BY [Month], [Region], [Store];

Explanation:

The CTE MonthlyStoreSales groups the raw rows to get one row per Month/Region/Store with MonthlySales.
The outer query applies the windowed SUM over MonthlySales, partitioned by Region/Store and ordered by Month, giving the cumulative (running) total per store across months.
This avoids multiple separate queries while preventing row-level running totals.

If grand totals or subtotals across dimensions are needed, GROUP BY ROLLUP or GROUP BY CUBE can be used on the aggregated result, as shown in the referenced documentation.

References:

Share via

Running total for grouped data

2 answers

Your answer