Calculate Incremental sum from running total Column

Question

I already have a running total column in my SQL Server table like below

I need to get the incremental sum( not sure whether it's the right word for it) of the values, for eg: 1000 + (1000-2000) + (2000-3000) + (3000-4000) + (4000-5000) = 5000

the end result should be like this so that I can get the sum of INC_Sum column

ID Value INC_Sum
1  1000   1000
2  2000   1000
3  3000   1000
4  4000   1000
5  5000   1000

the one that I've given above is an example and my table has got much more complex values in millions. I'm running SQL server 2008 R2, Can someone help me with this?

Use ranking function LEAD/LAG. https://stackoverflow.com/questions/23200189/sql-find-difference-between-previous-and-current-row — SAS, Aug 04 '17 at 11:35
@SAS `LEAD` and `LAG` is not available in SQL Server 2008 R2. — Rokuto, Aug 04 '17 at 11:38
Sorry. Then try this https://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/ Or use my join below. — SAS, Aug 04 '17 at 11:41

score 1 · Accepted Answer · answered Aug 04 '17 at 11:45

using outer apply() to get the previous value based on the order of id, and isnull() to substitute 0 for null values:

select 
    t.id
  , t.value
  , INC_Sum = t.value - isnull(x.previous_value,0)
from t
  outer apply (
    select top 1 
        previous_value = i.value
    from t as i
    where i.id < t.id
    order by i.id desc
    ) x

rextester demo: http://rextester.com/SYV5992

returns:

+----+-------+---------+
| id | value | INC_Sum |
+----+-------+---------+
|  1 |  1000 |    1000 |
|  2 |  2000 |    1000 |
|  3 |  3000 |    1000 |
|  4 |  4000 |    1000 |
|  5 |  5000 |    1000 |
+----+-------+---------+

Gordon Linoff · Answer 2 · 2017-08-04T11:45:46.563

You can use lag():

select id, value,
       (value - lag(value, 1, 0) over (order by id)) as inc_sum
from t;

Note that this uses the 3-argument form of lag. The second argument is the offset (number of rows to go back). The third is the default value.

Prior to SQL Server 2012, you would use outer apply:

select t.id, t.value,
       (t.value - coalesce(t2.value, 0) as inc_sum
from t outer apply
     (select top 1 t2.*
      from t t2
      where t2.id < t.id
      order by t2.id desc
     ) t2;

Or, if you know there are no gaps in the id (which might not be a reasonable assumption:

select t.id, t.value,
       (t.value - coalesce(t2.value, 0) as inc_sum
from t left join
     t t2
     on t2.id = t.id - 1;

1

Is LAG available in 2008 R2? – Keith Aug 04 '17 at 11:36

score 0 · Answer 3 · answered Aug 04 '17 at 11:37

I have seen this answered before here:

http://sqlfiddle.com/#!3/4e9e4

CREATE TABLE t
    ([id] int, [SomeNumt] int);

INSERT INTO t
    ([id], [SomeNumt])
VALUES
    (1, 10),
    (2, 12),
    (3, 3),
    (4, 15),
    (5, 23);

and

SELECT t1.id,
       t1.SomeNumt,
       SUM(t2.SomeNumt) AS SUM
FROM t t1
INNER JOIN t t2 ON t1.id >= t2.id
GROUP BY t1.id,
         t1.SomeNumt
ORDER BY t1.id

SAS · Answer 4 · 2017-08-04T11:51:01.843

SELECT t1.id,
   t1.SomeNum,
   t1.SomeNum - ISNULL(t2.SomeNum, 0) AS Delta
FROM t t1
LEFT OUTER JOIN t t2 ON t2.id = t1.id - 1
ORDER BY t1.id

or, if there are gaps in the id column:

SELECT t1.id,
   t1.SomeNum,
   t1.SomeNum - ISNULL(t2.SomeNum, 0) AS Delta
FROM t t1
LEFT OUTER JOIN t t2 ON t2.id = (SELECT MAX(t.id) FROM t WHERE t.id < t1.id)
ORDER BY t1.id

tested with:

create table t(id int, somenum int)
insert into t select 1,1 
insert into t select 2,2 
insert into t select 3,3 
insert into t select 5,4

Calculate Incremental sum from running total Column

4 Answers4