Chris Starling Chris Starling - 21 days ago 5
MySQL Question

MySQL query that returns all rows created before each month

I have a users table:

|id |created_at|
|---|----------|
|1 |2016-11-15|
|2 |2016-08-30|
|3 |2016-06-23|
|4 |2015-12-03|


I want to run a query that returns each month and how many rows have been created up until that month.
The query I have now is:

SELECT YEAR(created_at) AS `year`, MONTH(created_at) AS `month`, COUNT(*) AS `count`
FROM users
GROUP BY `year`, `month`
ORDER BY `year` DESC, `month` DESC


which returns the following table:

|year|month|count|
|----|-----|-----|
|2016|11 |1 |
|2016|8 |1 |
|2016|6 |1 |
|2015|12 |1 |


The table I want looks like this:

|year|month|count|
|----|-----|-----|
|2016|11 |4 |
|2016|8 |3 |
|2016|6 |2 |
|2015|12 |1 |


Or even better:

|year|month|count|
|----|-----|-----|
|2016|11 |4 |
|2016|10 |3 |
|2016|9 |3 |
|2016|8 |3 |
|2016|7 |2 |
|2016|6 |2 |
|2016|5 |1 |
|2016|4 |1 |
|2016|3 |1 |
|2016|2 |1 |
|2016|1 |1 |
|2015|12 |1 |
|2015|11 |0 |

Ton Ton
Answer

try

SET @tot=0;
SELECT max(rank) AS `cm` ,ym,count(*) AS `ct` 
FROM (
SELECT concat_ws('',YEAR(created_at) ,LPAD( MONTH(created_at),2,'0') ) AS `ym` ,@tot:=@tot + 1 AS `rank` FROM users
ORDER BY ym DESC) as a
GROUP BY ym