MySQL Rolling SUM

Question

For example I have the bellow table

 DATE       QUANTITY
9/10/2017     3
9/10/2016     5
9/03/2015     6
9/10/2017     2
8/10/2017     9

And I Want the final output to look like below

YEAR    MONTH   ROLLING SUM   SUM
2015     03          6         6
2016     10          11        5
2017     10          25        14

To get the sum I use the following code

SELECT YEAR(Date),MONTH(Date),SUM(QUANTITY) 
FROM table
GROUP BY YEAR(Date),MONTH(Date);

Is it possible to add the Rollup Sum to the same query?

As a second note: At the moment I am using MySQL but the final enviroment that these queries will be running is AWS Athena.And I was wondering if there is a middle ground between the two to test these queries.

Answer 1

In most databases you can use window functions:

SELECT YEAR(Date), MONTH(Date), SUM(QUANTITY),
       SUM(SUM(QUANTITY)) OVER (ORDER BY YEAR(DATE), MONTH(DATE) 
FROM table
GROUP BY YEAR(Date),MONTH(Date);

In MySQL, I would recommend a correlated subquery:

SELECT YEAR(Date), MONTH(Date), SUM(QUANTITY),
       (SELECT SUM(t2.QUANTITY)
        FROM table t2
        WHERE YEAR(t2.Date) < YEAR(t.Date) OR
              (YEAR(t2.Date) = YEAR(t.Date) AND MONTH(t2.Date) <= MONTH(t.date))
       ) as rolling_sum
FROM table t
GROUP BY YEAR(Date), MONTH(Date);