Consider a table with customers, sorted dates and amounts as follows
User Date Purchase
Joe '2020-01-01' 10
Joe '2020-02-01' 20
Joe '2020-02-20' 20
Joe '2020-03-15' 15
Lucy '2020-01-12' 5
Lucy '2020-02-15' 30
Lucy '2020-02-21' 20
Lucy '2020-03-05' 30
I would like to obtain a new column with the cumulative spent with the previous purchases, i.e.,
User Date Purchase Cum
Joe '2020-01-01' 10 10
Joe '2020-02-01' 20 30
Joe '2020-02-20' 20 50
Joe '2020-03-15' 15 65
Lucy '2020-01-12' 5 5
Lucy '2020-02-15' 30 35
Lucy '2020-02-21' 20 55
Lucy '2020-03-05' 30 85
This would be:
select t.*,
sum(purchase) over (partition by user order by date) as running_sum
from t;
You can use window function :
sum(purchase) over (partition by user order by date) as purchase_sum
if window function not supports then you can use correlated subquery :
select t.*,
(select sum(t1.purchase)
from table t1
where t1.user = t.user and t1.date <= t.date
) as purchase_sum
from table t;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With