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?
>Solution :
This should do the trick (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