Calculate cumulative sum over partitions with gaps in SQL

Question

I have a table with stock changes with different countries and different products. The table looks like the following:

Date	Country	Product	Variation
2023-10-01	Spain	Pen	1
2023-10-01	Germany	Pen	1
2023-10-01	Germany	Pen	-1
2023-9-01	Italy	Pen	1
2023-09-01	Italy	Pen	5
2023-09-01	Germany	Pencil	2
2023-08-01	Spain	Pencil	1

I am trying to write a query on mysql 8+ that can summarize the stock of the warehouse grouped by month, by product and by country.

The result should be something like this for October and September:

Date	Country	Product	Stock
October 23	Italy	Pencil	5
October 23	Italy	Pen	1
October 23	Spain	Pencil	2
October 23	Spain	Pen	3
October 23	Germany	Pencil	2
October 23	Germany	Pen	3
September 23	Italy	Pencil	2
September 23	Italy	Pen	1
September 23	Spain	Pencil	4
September 23	Spain	Pen	1
September 23	Germany	Pencil	2
September 23	Germany	Pen	3

The problem is that there are gaps in some countries or some products that do not allow me to perform a simple cumulative sum with partitions. For example:

SELECT
Date,
Product,
Country,
SUM(SUM(variation)) OVER (PARTITION BY Product, Country ORDER BY Date)
FROM mytable
GROUP BY Date,Country,Product

The only solution I can think of is to create a procedure with loops on the dates and calculate the totals at each loop, is there any other more efficient solution to avoid the gaps? Maybe using recursive CTE ?

Thank you,

Answer 1

What does this give you?

SELECT  DATE_FORMAT(DATE, "%M-%y"),
        Product,
        Country,
        SUM(variation) AS sum_v
    FROM  mytable
    WHERE Date >= '2023-09-01'
      AND Date  < '2023-09-01' + INTERVAL 2 MONTH
    GROUP BY  Date, Country, Product 

I don't understand the issue with gaps.

As Akina points out, a LEFT JOIN with a table containing all date would give you NULLs for days with no data. IFNULL() or COALESCE() can then turn NULL into 0.

If the above does not give you the final result, show us what you need.