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,
x
Growth RollingSum TMonth TYear511 511 AUG 201979 590 SEP 2019-6 584 OCT 20190 584 NOV 2019-4 580 DEC 201945 625 JAN 2020-1 624 FEB 20207 631 MAR 2020-22 609 APR 2020-6 603 MAY 2020-20 583 JUN 20200 583 JUL 2020My 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 TYear511 583 AUG 201979 583 SEP 2019-6 583 OCT 20190 583 NOV 2019-4 583 DEC 201945 583 JAN 2020-1 583 FEB 20207 583 MAR 2020-22 583 APR 2020-6 583 MAY 2020-20 583 JUN 20200 583 JUL 2020I 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, TYearFROM 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