How to cummulative sum in MySql with grupBy and interval?

Question

Table Sales

date        id_product  total sales
2018-10-01  1             40
2019-09-01  1             20
2019-11-01  1             5
2019-12-01  1             40
2020-01-01  1             10  
2020-02-01  1             15    
2020-03-01  1             20
2020-08-01  1             10
2021-01-01  1             5
2021-02-01  1             8
2021-04-01  1             12

Table Product

id      name
1       Book
2       Pen

How to query in MySql to get the number (serial/sequential number) and the total cumulative sales with an interval of 3 years to get results like this?

number name  date  sum_sales   cummulative_sales
1      Book  2018  40          40
2      Book  2019  65          105
3      Book  2020  55          160
4      Book  2021  25          145

Answer 1

Assuming you are running MySQL 8+, you may try using SUM as an analytic function:

SELECT
    ROW_NUMBER() OVER (PARTITION BY p.name ORDER BY YEAR(s.date)) number,
    p.name,
    YEAR(s.date) AS date,
    SUM(s.total_sales) AS sum_sales,
    SUM(SUM(s.total_sales)) OVER (PARTITION BY p.name ORDER BY YEAR(s.date)) AS cummulative_sales
FROM Sales s
INNER JOIN Product p
    ON p.id = s.id_product
GROUP BY
    p.name,
    YEAR(s.date)
ORDER BY
    p.name,
    YEAR(s.date);

Answer 2

Using MySQL 5.7, this will give you a sum and a cumulative sum of sales in a window of 3 years.

SELECT (@row = @row + 1) AS number, name, YEAR(date) AS date, 
SUM(total_sales) as sum_sales,
iF(
    @window > 0, 
    @cummulative_sales := @cummulative_sales + SUM(total_sales), 
    @cummulative_sales := SUM(total_sales)
) AS cummulative_sales,
@window := @window + 1,
@window := @window % 3

FROM Sales
JOIN Product ON Sales.id_product = Product.id,
(
    SELECT @row := 0, @cummulative_sales := 0, @window := 0
) as a
GROUP BY name, YEAR(date);