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

For example, I have this data:

And the result should be the same as below:

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

How can I solve this problem?

CodePudding user response：

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

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

CodePudding user response：

Can you try this,

SELECT
    customer
    , year
    , month
    , ISNULL(LAG(no_orders) OVER (PARTITION BY customer ORDER BY customer, year, month),0) Cum_Orders
FROM (
    SELECT
        DISTINCT customer, year, month
        , SUM(no_orders) OVER (PARTITION BY customer ORDER BY customer, year, month) no_orders 
    FROM ABC
) a