how to add column value in next colum

Question

id        value
1          10
2          20
3          30
4          40
5          50

Required output

table name data.

 id       value 
1         10    //( 10+0(previous value))
2         30   //( 20+10(previous value))
3         50   //( 30+20(previous value))
4         70   //( 40+30(previous value))
5         90    //(50+40(previous value))

please provide sql query

I removed the incompatible database tags. Please tag only with the database you are really using. — Gordon Linoff, Apr 04 '17 at 11:25
Possible duplicate of [how to get cumulative sum](http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — René Hoffmann, Apr 04 '17 at 11:28

score 1 · Accepted Answer · answered Apr 04 '17 at 11:43

1

You are looking for LAG which is standard SQL and should be available in later DB2 versions if I'm not mistaken.

select
  id, 
  value + coalesce( lag(value) over (order by id), 0 ) as value
from mytable
order by id;

In case LAG OVER is not available, SUM OVER may be:

select
  id, 
  coalesce( sum(value) over (order by id rows between 1 preceding and current row), 0 )
    as value
from mytable
order by id;

answered Apr 04 '17 at 11:43

Thorsten Kettner

89,309
7
49
73

when execute query than that error comes select id, coalesce( sum(value) over (order by id rows between 1 preceding and current row), 0 ) as value from users.mohit order by id "VALUE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.18.60 – raj Apr 04 '17 at 11:53
Sorry, I don't see how it can come to this message. `value` is used in two places only: `sum(value)` which seems correct and `as value` which seems correct also. I don't know DB2 well. Does it have a problem, because we use the same name for the alias as for the column? I don't know. – Thorsten Kettner Apr 04 '17 at 12:08

score 0 · Answer 2 · answered Apr 24 '17 at 09:41

0

solution 1:

select f1.id, 
ifnull((select f2.value from yourtable f2 where f1.id - 1 =f2.id), 0) + f1.value as value
from yourtable f1

answered Apr 24 '17 at 09:41

Esperento57

16,521
3
39
45

score 0 · Answer 3 · answered Apr 24 '17 at 09:43

0

solution 2:

select f1.id, 
ifnull(f3.value, 0) + f1.value as value
from yourtable f1
left outer join lateral
(
    select f2.value from yourtable f2 
    where f1.id - 1 =f2.id
) f3 on 1=1

answered Apr 24 '17 at 09:43

Esperento57

16,521
3
39
45

how to add column value in next colum

3 Answers3