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