Create a running subtotal for SQL Server

Question

I am trying to get a running subtotal (understanding this is different from subtotals for groups, and the rollup approach).

Tried using

Row_Number() over (order by ID_Number) as Row_Count

and nesting it in select statements and using a LEFT OUTER JOIN on itself (which just churns).

What I am trying to get is this:

if ROW_COUNT > 1 THEN RUNNINGTOTAL = Volume_Category + (RUNNINGTOTAL for ID_Number where ROW_COUNT= ROW_COUNT(for this ID_Number*)-1)

I have a table with a list of unique "ID-Numbers" which are the focus here.

There are a few questions on this, usually labeled cumulative sum. I like the answers with temp variables, like this: http://stackoverflow.com/a/7631964/931379. They are usually much much faster than clever uses of the `sum` function. — Pursuit, Jan 22 '13 at 20:42
@Pursuit the quirky update (things like `UPDATE SET @var = Total = @var + value`) is not guaranteed to process in any specific order, even if that's what you observe. Please see http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals for some details on this. — Aaron Bertrand, Jan 22 '13 at 20:50
@pursuit I searched for answers before asking my own. Needed to use 'cumulative' and not 'running subtotal' — Frank Horatio, Jan 22 '13 at 21:02

score 1 · Answer 1 · answered Jan 22 '13 at 20:39

1

Unless you are using SQL Server 2012, the easiest way to do a cumulative sum is with a correlated subquery. Here is the template for the code:

select t.*,
       (select sum(val) from t t2 where t2.ordercol <= t.ordercol) as cumesum
from t

In 2012, you can do:

select t.*,
       sum(val) over (order by ordercol) as cumesum
from t

In both these, val is the column you want to sum and ordercol is how the ordering is specified.

answered Jan 22 '13 at 20:39

Gordon Linoff

@bluefeet . . . LOL, I think you've made that suggestion before. Will I ever learn? – Gordon Linoff Jan 22 '13 at 20:41
I don't understand what is the second query! – Hamlet Hakobyan Jan 22 '13 at 20:41
@HamletHakobyan It's for SQL Server 2012! – Lamak Jan 22 '13 at 20:44
@Lamak Are you sure is it returns cumulative sum? Can anyone give example on sql fiddle? Greate soltion! – Hamlet Hakobyan Jan 22 '13 at 20:45
1

Cursor actually wins out here at scale in my tests on < SQL 2012. And the SQL Server 2012 solution is pretty pitiful too unless you explicitly state `ROWS UNBOUNDED PRECEDING` - by default this will use `RANGE`, which spools to disk automatically. http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals – Aaron Bertrand Jan 22 '13 at 20:52
I need to add filters so the first anser from Gordon Linoff works. Thanks for the help. I am a SQL newbie who last programmed in Pascal. – Frank Horatio Jan 22 '13 at 20:58

score 0 · Answer 2 · answered Jan 22 '13 at 20:44

0

Try this:

SELECT
   T1.Id,
   SUM(T2.Amount) Total
FROM tbl T1
   JOIN Tbl T2
      ON T1.Id>= T2.Id
GROUP BY T1.Id

answered Jan 22 '13 at 20:44

Hamlet Hakobyan

2 Answers2