How to calculate the cumulative sum in SQL over rows

Question

I have the following SQL table:

StockID  Flow   Stock
1        +      2
1        -      3
1        +      8

I have another table:

ID   InStock
1    22
2    51
3    92

I want to select the first value of the second table 22 and add or subtract it cumulatively from the values Stock of the first table with the StockID = ID, based on the sign in the flow column. Then add/subtract the values in the column Stock from this solution.

This should appear in a new column Sol:

ID    Flow   Stock    Sol
1      +      2        24    (22+2)
2      -      3        21    (24-3)
3      +      8        29    (21+8)

Do you have any ideas?

Check out this post https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — sawbeanraz, Aug 08 '18 at 16:33
Where's the common column??? Without a common column, you can't join the 2 tables. — Eric, Aug 08 '18 at 16:33
You have simplified this so much it isn't answerable. You need something to use for ordering. You need something to join on. But in the end this is just a running total made a little bit odd by the flow column. But that is trivial to deal with. Just requires a few silly string manipulations to turn your string into a usable formula. — Sean Lange, Aug 08 '18 at 16:42
Are you actually using sql 2008? There are ways to do this but with 2012+ it is simpler and more performant. — Sean Lange, Aug 08 '18 at 16:50

score 1 · Accepted Answer · answered Aug 08 '18 at 17:31

You need to add some kind of ordering column to the first table. Otherwise it doesn't make sense. I decided to add the extra column seq that seves this purpose.

Therefore, with the following data:

create table flow (
  stockid int,
  seq int,
  flow char(1),
  stock int
);

insert into flow (stockid, seq, flow, stock) values (1, 10, '+', 2);
insert into flow (stockid, seq, flow, stock) values (1, 11, '-', 3);
insert into flow (stockid, seq, flow, stock) values (1, 12, '+', 8);

create table stock (
  id int,
  instock int
);

insert into stock (id, instock) values (1, 22);
insert into stock (id, instock) values (2, 51);
insert into stock (id, instock) values (3, 92);

The query is:

select s.id, f.seq, f.flow, f.stock,
    s.instock + 
      sum(case when f.flow = '+' then 1 else -1 end * f.stock)
      over(partition by s.id order by f.seq) as sol
  from stock s
  left join flow f on f.stockid = s.id;

Result:

id  seq     flow    stock   sol          
--  ------  ------  ------  ------
1   10      +       2       24    
1   11      -       3       21    
1   12      +       8       29    
2   <null>  <null>  <null>  <null>
3   <null>  <null>  <null>  <null>

score 0 · Answer 2 · answered Aug 08 '18 at 17:44

In SQL Server 2008, you are stuck with apply for calculating the cumulative sum:

select t1.*, tt1.stock + t2.instock
from t1 cross apply
     (select sum(case when flow = '+' then tt1.sock else - tt1.stock end) as stock
      from t1 tt1
      where tt1.id <= t1.id
     )  tt1 cross join
     (select top (1) t2.*
      from t2
      order by id
     ) t2;

How to calculate the cumulative sum in SQL over rows

2 Answers2