Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Cumulative sum of previous rows for each partition

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading