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: stackoverflow.com/a/7631964/931379. They are usually much much faster than clever uses of the sum function. — Pursuit
– Pursuit, Commented Jan 22, 2013 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 sqlperformance.com/2012/07/t-sql-queries/running-totals for some details on this. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 22, 2013 at 20:50
@pursuit I searched for answers before asking my own. Needed to use 'cumulative' and not 'running subtotal' — Frank Horatio
– Frank Horatio, Commented Jan 22, 2013 at 21:02

Gordon Linoff · Accepted Answer · 2013-01-22 20:39:06Z

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, 2013 at 20:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gordon Linoff Over a year ago

@bluefeet . . . LOL, I think you've made that suggestion before. Will I ever learn?

Hamlet Hakobyan Over a year ago

I don't understand what is the second query!

Lamak Over a year ago

@HamletHakobyan It's for SQL Server 2012!

Hamlet Hakobyan Over a year ago

@Lamak Are you sure is it returns cumulative sum? Can anyone give example on sql fiddle? Greate soltion!

Aaron Bertrand Over a year ago

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. sqlperformance.com/2012/07/t-sql-queries/running-totals

|

Hamlet Hakobyan · Accepted Answer · 2013-01-22 20:44:00Z

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, 2013 at 20:44

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

Create a running subtotal for SQL Server

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related