CODEWITHSUNDEEP
sqlsql-server
jmasterx
jmasterx

Reputation: 54113

Getting previous value + new value as a column?

say I have this:

select money from somewhere

I want now another column called accumulatedMoney which is going to be = to accumulatedMoney of previous row + money of current row Ex:

m = 2, am = 2
m = 3, am = 5
m = 3, am = 8

...

What can I do to achieve this? Thanks

Upvotes: 0

Views: 128

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

In any database, you can do this with a correlated subquery:

select t.am, t.m,
       (select sum(tprev.m) from t tprev where tprev.am <= t.am) as cumsum
from t

In any database, you can also do this as a join and group by:

select t.am, t.m, sum(tprev.m) as cumsum
from t join
     t tprev
     on tprev.am <= t.am
group by t.am, t.m

In databases that support cumulative sums, you can do it as:

select t.am, t.m,
       sum(t.m) over (order by t.am) as cumsum
from t

(SQL Server 2012 and Oracle support this.)

Upvotes: 2

Related Questions