SQL - cumulative sum in postgres

Question

I have my data like this:

item - initial_value - amount - dateofpurchase 
A       100             -3      2018-11-22          
A       100             -2      2018-11-22      
B       200             -5      2018-11-22
B       200             6       2018-11-22
B       200             -1      2018-11-22

(everything is ordered by date and time)

I want to calculate this column, that shows how much stock do you have after each step and taking in count the last amount

item - initial_value - amount - dateofpurchase - cumulative
A       100             -3      2018-11-22          97
A       100             -2      2018-11-22          95
B       200             -5      2018-11-22          195
B       200             6       2018-11-22          201
B       200             -1      2018-11-22          200

I've been trying a sum function with unbounded preceding and current row with no luck

Gordon Linoff · Accepted Answer · 2018-11-23 12:51:26Z

1

You can use window functions and subtraction:

select t.*,
       ( initial_amount +
         sum(amount) over (partition by item order by date_of_purchase)
       ) as cumulative
from t;

answered Nov 23, 2018 at 12:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2018-11-23 13:15:51Z

use window function

with cte as
(
    select 'A' item, 100 as initial_value, -3 amount, '2018-11-22'::date as dateofpurchase
    union all
    select 'A' ,100, -2, '2018-11-22'
    union all
    select 'B',200, -5,'2018-11-22'
    union all
    select 'B',200, 6,'2018-11-22' 
    union all
    select 'B',200, -1,'2018-11-22' 
) 
,  t1 as
(select t.*, row_number() over(partition by item order by dateofpurchase) rn

from cte t
 ) 
 , t3 as
 (select *, case when rn=1 then initial_value else 0 end as val   from t1
  ) select item,initial_value,amount,dateofpurchase, sum(val+amount) over(partition by item order by rn) as cumulative  from t3

Sample output

item    initial_value   amount  dateofpurchase  cumulative
A          100          -3        2018-11-22    97
A          100          -2        2018-11-22    95
B          200          -5        2018-11-22    195
B          200           6        2018-11-22    201
B          200          -1        2018-11-22    200

demo link

Collectives™ on Stack Overflow

SQL - cumulative sum in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related