CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bob
Bob

Reputation: 191

How to calculate the cumulative sum in SQL over rows

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?

Upvotes: 0

Views: 5280

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

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>

Upvotes: 1

Related Questions