Sum of values from previous row columns + value of current row

Question

I have table like this:

id  valueA  valueB  ValueC 
--------------------------   
1   2       3       1         
2   3       2       0       
3   1       2       2

I would like to get:

id  valueA  valueB  ValueC  GSumABC
----------------------------------   
1   2       3       1       6  
2   3       2       0       11
3   1       2       2       16

What would be the best way to do this? Using temporary tables, joins or something else?

Possible duplicate of [Cumulating value of current row + sum of previous rows](https://stackoverflow.com/questions/12668785/cumulating-value-of-current-row-sum-of-previous-rows) — Valerica, Nov 09 '17 at 11:24

score 1 · Accepted Answer · answered Nov 09 '17 at 11:39

In SQL Server 2008, you can use cross apply:

select t.*, tt.GSumABC
from t cross apply
     (select sum(valueA + valueB + ValueC) as GSumABC
      from t t2
      where t2.id <= t.id
     ) tt;

You can also phrase this as a correlated subquery:

select t.*,
       (select sum(valueA + valueB + ValueC) as GSumABC
        from t t2
        where t2.id <= t.id
       ) as GSumABC
from t;

score 1 · Answer 2 · answered Nov 09 '17 at 12:38

1

     select valueA,valueB,valueC,SUM(temp) over (order by t2.Id) as GTOTAL from
 temptable t1 
    left join (SELECT Id,(valueA + valueB + valueC ) as temp FROM temptable)
 as t2 on t1.Id = t2.Id ;

answered Nov 09 '17 at 12:38

A.D.

2,352
2
15
25

score 0 · Answer 3 · answered Nov 09 '17 at 11:35

with dat
as
(
  select 1 id,2 valueA,3 ValueB,1 ValueC union all
  select 2,3,2,0  union all      
  select 3,1,2,2 
)
select dat.id,
       dat.ValueA,dat.ValueB,dat.ValueC ,
       dat.ValueA+dat.ValueB+dat.ValueC total ,
       dat.ValueA+dat.ValueB+dat.ValueC+
        sum(case when dat_below.id<dat.id then 
                    dat_below.ValueA+dat_below.ValueB+dat_below.ValueC 
                 else 0 
            end ) rolling
from  dat cross join dat dat_below where dat_below.id <= dat.id
group by dat.id,
       dat.ValueA,dat.ValueB,dat.ValueC,dat.ValueA+dat.ValueB+dat.ValueC
order by 1

Sum of values from previous row columns + value of current row

3 Answers3