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
Advertisement
Answer
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