Rolling sum for a column in mysql

Question

I would like to get the rolling sum for the growth column for the following sample data which can be found here dbfiddle

The output should look like,

Growth  RollingSum  TMonth  TYear
511       511       AUG     2019
79        590       SEP     2019
-6        584       OCT     2019
0         584       NOV     2019
-4        580       DEC     2019
45        625       JAN     2020
-1        624       FEB     2020
7         631       MAR     2020
-22       609       APR     2020
-6        603       MAY     2020
-20       583       JUN     2020
0         583       JUL     2020

My attempt is as follows. I am only getting the total for values in the Growth column

 SELECT r1.Growth,  sum(r2.Growth) AS rolling_total ,r1.Month,r1.Year 
    FROM Report AS r1 JOIN Report AS r2
        ON r1.Month = r1.Month
    GROUP BY r1.Month;

The above gives me the result as,

Growth  RollingSum  TMonth  TYear
511      583         AUG    2019
79       583         SEP    2019
-6       583         OCT    2019
0        583         NOV    2019
-4       583         DEC    2019
45       583         JAN    2020
-1       583         FEB    2020
7        583         MAR    2020
-22      583         APR    2020
-6       583         MAY    2020
-20      583         JUN    2020
0        583         JUL    2020

I want to avoid using procedures,cursors or functions if at all possible. I am using mysql 8.0.17

Answer 1

Since you are using mysql 8 use window functionn SUM

DATE_FOMAT are not working, so you need STR_TO_DATE

CREATE TABLE roll_month (
  `Growth` INTEGER,
  `RollingSum` INTEGER,
  `TMonth` VARCHAR(3),
  `TYear` INTEGER
);

INSERT INTO roll_month
  (`Growth`, `RollingSum`, `TMonth`, `TYear`)
VALUES
  ('511', '511', 'AUG', '2019'),
  ('79', '590', 'SEP', '2019'),
  ('-6', '584', 'OCT', '2019'),
  ('0', '584', 'NOV', '2019'),
  ('-4', '580', 'DEC', '2019'),
  ('45', '625', 'JAN', '2020'),
  ('-1', '624', 'FEB', '2020'),
  ('7', '631', 'MAR', '2020'),
  ('-22', '609', 'APR', '2020'),
  ('-6', '603', 'MAY', '2020'),
  ('-20', '583', 'JUN', '2020'),
  ('0', '583', 'JUL', '2020');

SELECT
`Growth`
,  SUM(`Growth`) OVER(  ORDER BY STR_TO_DATE(CONCAT('01-',`TMonth`,'-',`TYear`), '%d-%M-%Y')  ROWS UNBOUNDED PRECEDING)  RollingSum  
, `TMonth`, `TYear` 
FROM roll_month

Growth | RollingSum | TMonth | TYear
-----: | ---------: | :----- | ----:
   511 |        511 | AUG    |  2019
    79 |        590 | SEP    |  2019
    -6 |        584 | OCT    |  2019
     0 |        584 | NOV    |  2019
    -4 |        580 | DEC    |  2019
    45 |        625 | JAN    |  2020
    -1 |        624 | FEB    |  2020
     7 |        631 | MAR    |  2020
   -22 |        609 | APR    |  2020
    -6 |        603 | MAY    |  2020
   -20 |        583 | JUN    |  2020
     0 |        583 | JUL    |  2020

db<>fiddle here

Answer 2

You can use SUM() window function but with the correct ordering.
This ordering:

ORDER BY DATE_FORMAT(`TMonth`, '%M'), `TYear`

is wrong because DATE_FORMAT() with these parameters returns NULL, so you simply sort by Year.
See the demo.
It is coincidental that you get correct results.

The correct ordering is:

ORDER BY STR_TO_DATE(CONCAT(TYear, TMonth, '01'), '%Y%b%d')

So use this:

SELECT
  Growth,
  SUM(Growth) OVER (ORDER BY STR_TO_DATE(CONCAT(TYear, TMonth, '01'), '%Y%b%d')) RollingSum,
  TMonth,
  TYear
FROM Report 

See the demo.
Results:

> Growth | RollingSum | TMonth | TYear
> -----: | ---------: | :----- | ----:
>    511 |        511 | AUG    |  2019
>     79 |        590 | SEP    |  2019
>     -6 |        584 | OCT    |  2019
>      0 |        584 | NOV    |  2019
>     -4 |        580 | DEC    |  2019
>     45 |        625 | JAN    |  2020
>     -1 |        624 | FEB    |  2020
>      7 |        631 | MAR    |  2020
>    -22 |        609 | APR    |  2020
>     -6 |        603 | MAY    |  2020
>    -20 |        583 | JUN    |  2020
>      0 |        583 | JUL    |  2020

Answer 3

With the values and names that i took from your table, this cross join would give you the output you want way more easy:

select r1.Growth, r1.TMonth, TYear,
(@s := @s + r1.Growth) as RollingSum
from Report r1 cross join
    (select @s := 0) p;