Cumulative sum of previous rows for each partition

Question

I want to calculate the cumulative sum of monthly orders for each customer in my database.

For example, I have this data:

customer	year	month	no_orders
1544	2022	4	5
1544	2022	4	1
1544	2022	12	1
1544	2023	1	3

And the result should be the same as below:

customer	year	month	cumulative no_orders
1544	2022	4	0
1544	2022	12	6
1544	2023	1	7

I used lag() and in the next step, sum() over () but my result was false!

How can I solve this problem?

Answer 1

As @Larnu advises in the comments

Seems like you need to do several steps here. Aggregate (and group) into months first, and then use a cumulative SUM but have the window not include the current row.

Some SQL to implement this idea is below (DB FIDDLE)

SELECT customer,
       year,
       month,
       cumulative_no_orders = ISNULL(SUM(SUM(no_orders))
                                       OVER (
                                         PARTITION BY customer
                                         ORDER BY year, month 
                                         ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
                                     , 0)
FROM   YourTable
GROUP  BY customer,
          year,
          month 

It first does the aggregation

SELECT customer,
       year,
       month,
       sum_no_orders = SUM(no_orders)
FROM   YourTable
GROUP  BY customer, year, month 

to return the following

customer	year	month	sum_no_orders
1544	2022	4	6
1544	2022	12	1
1544	2023	1	3

and then calculates the running total of sum_no_orders from previous rows on top of that.