SQL Cumulative sum in a partition by

Question

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

Tag the DBMS that you are using. – Yogesh Sharma Apr 17 '20 at 11:41 — Yogesh Sharma, Apr 17 '20 at 11:41

score 4 · Accepted Answer · answered Apr 17 '20 at 11:39

4

This would be:

select t.*,
       sum(purchase) over (partition by user order by date) as running_sum
from t;

answered Apr 17 '20 at 11:39

Gordon Linoff

1,242,037
58
646
786

1

Is the order by the key to achieve the running sum (instead of a total sum)? – simon Apr 17 '20 at 11:54
1

@Jos . . . Yes. The `order by` turns this into a running sum. – Gordon Linoff Apr 17 '20 at 12:33

score 1 · Answer 2 · answered Apr 17 '20 at 11:40

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;

score 1 · Answer 3 · answered Apr 17 '20 at 11:41

1

Try partition by

    Select  User, Date, Purchase, 
    sum(purchase ) 
    over (partition by User order by user )
     From table

answered Apr 17 '20 at 11:41

Himanshu

3,830
2
10
29

SQL Cumulative sum in a partition by

3 Answers3