mysqlsumcumulative-sum

Get running totals for each month based on created_at date

I'm trying to get a running total for each month of this year. So my ideal result would be something such as:

January   |  4 
February  |  5
March     |  8
April     | 10
May       | 12
June      | 14
July      | 16
August    | 17
September | 18
October   | 21
November  | 22
December  | 22

The basic count would just check against first of the month such as:

January 1 (sum where created_at < January 1, 2018)
February 1 (sum where created_at < February 1, 2018)
March 1 (sum where created_at < March 1, 2018)
April 1 (sum where created_at < April 1, 2018)
...

Doing it for one month at a time is easy, as I can just do something like this:

SELECT *
FROM businesses
WHERE created_at < CONCAT(YEAR(CURDATE()), "-12-01 00:00:01")

I tried using one of the examples from another Stackoverflow answer, but it's not working quite as desired as it seems like the sort or something is messed up so the counts are not lining up right.

You can see schema build and current SQL here: http://sqlfiddle.com/#!9/0c23cc/20

Solution

  • Try something like this.

    SELECT
MONTHNAME( created_at ) AS theMonth,
(
    SELECT
        count( * ) 
    FROM
        businesses AS u1 
    WHERE
        YEAR ( u1.created_at ) = YEAR ( businesses.created_at ) 
        AND u1.created_at <= date_ADD( last_day( businesses.created_at ), INTERVAL 1 DAY ) 
) AS totals 
FROM
    businesses 
WHERE
    YEAR ( businesses.created_at ) = YEAR ( CURDATE( ) ) 
GROUP BY
    MONTHNAME( businesses.created_at ) 
ORDER BY
    MONTH ( businesses.created_at ) ASC