Daily running total for each month in MySQL

Question

I have a table with daily count data, which looks something like this:

Date         Count
2019-12-01     100
2019-12-02     200
2019-12-03     300
...            ...
2020-01-01      10
2020-01-02     200
2020-01-03     500

I am trying to compute the running total on a daily basis for each month. The desired result should look like this:

Date         Count    Running Total
2019-12-01     100              100
2019-12-02     200              300
2019-12-03     300              600
...            ...
2020-01-01      10               10
2020-01-02     200              210
2020-01-03     500              710
...

I managed to compute a running total for a single month, however I am struggling to get the running totals for multiple months with a single query. Anyone knows how to do or can provide an idea to do it only using SQL? I am running MySQL 5.7.

Best

Answer 1

You can use Session variables like

CREATE TABLE table1
    (`Date` date, `Count` int)
;

INSERT INTO table1
    (`Date`, `Count`)
VALUES
    ('2019-12-01', 100),
    ('2019-12-02', 200),
    ('2019-12-03', 300),
    ('2020-01-01', 10),
    ('2020-01-02', 200),
    ('2020-01-03', 500)
;

✓

✓

SELECT
  `Date`
  , `Count`
  ,running_coint
FROM
(SELECT 
  `Date`
  , `Count`
  , IF(MONTH(@date) =  MONTH(`Date`),@running_coint := @running_coint + `Count`,@running_coint := `Count`) running_coint
,  @date := `Date`
FROM (SELECT * FROM table1 ORDER BY `Date`) t,(SELECT @running_coint := 0) a,(SELECT @date := '1999-01-01') b) t1
ORDER BY`Date`;

Date       | Count | running_coint
:--------- | ----: | ------------:
2019-12-01 |   100 |           100
2019-12-02 |   200 |           300
2019-12-03 |   300 |           600
2020-01-01 |    10 |            10
2020-01-02 |   200 |           210
2020-01-03 |   500 |           710

db<>fiddle here

Answer 2

SELECT SUM(Count) AS "Total count" FROM your_table WHERE Date < '2019-12-01';

Obviously the date would be whatever you want to check on.