1

I'm trying to build a query that shows the total number of users for every month/year. The query I have right now shows me the users created in any particular month/year point. But what I want is the sum of users till that point, not just the ones created in that period of time. This is the table structure:

mysql> desc users;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username          | int(11)          | NO   |     | NULL    |                |
| password          | varchar(255)     | NO   |     | NULL    |                |
| email             | varchar(255)     | NO   |     | NULL    |                |
| legajo            | int(10) unsigned | NO   |     | NULL    |                |
| doc_tipo          | varchar(45)      | NO   |     | NULL    |                |
| doc_nro           | int(10) unsigned | NO   |     | NULL    |                |
| activo            | int(10) unsigned | NO   |     | 0       |                |
| token_actv        | varchar(255)     | NO   |     | NULL    |                |
| token_fecha_envio | datetime         | NO   |     | NULL    |                |
| created           | datetime         | YES  |     | NULL    |                |
| modified          | datetime         | YES  |     | NULL    |                |
| role              | varchar(60)      | NO   |     | usuario |                |
| nombres           | varchar(255)     | YES  |     | NULL    |                |
| apellido          | varchar(255)     | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

This is the query.

SELECT CONCAT_WS('/',Month(created),YEAR(created)), Count(*)
FROM users
GROUP BY Month(created),YEAR(created)
ORDER BY Month(created) ASC ,YEAR(created) ASC

Results from current query looks like:

mysql> SELECT CONCAT_WS('/',Month(created),YEAR(created)), Count(*) FROM users GROUP BY Month(created),YEAR(created) ORDER BY Month(created) ASC ,YEAR(created) ASC LIMIT 5;
+---------------------------------------------+----------+
| CONCAT_WS('/',Month(created),YEAR(created)) | Count(*) |
+---------------------------------------------+----------+
| 1/2010                                      |       79 |
| 1/2011                                      |       70 |
| 1/2012                                      |       70 |
| 1/2013                                      |       80 |
| 1/2014                                      |       64 |
+---------------------------------------------+----------+

Any help will be greatly appreciated.

sms
  • 393
  • 6
  • 20
  • So the result you want is 219 for 1/2012 and 299 for 1/2013, etc? – ethan May 22 '15 at 21:37
  • Yes! English is not my native language so I'm sorry if the question is ambiguous. – sms May 22 '15 at 21:39
  • This will be difficult to do with mysql alone. Are you serving these results to a server side application? Could you just compute the numbers you want within the application? – ethan May 22 '15 at 21:43
  • @ethan comment seems to be the easiest and most straightforward solution. However, can't you count the users grouped by month (in a format the RDMS can directly work with) and selft join this results on month_right <= month_left and then sum up the users by grouping by month_left? – PhillipD May 22 '15 at 21:50
  • actually its quite easy to do with sql. just a minute – pala_ May 22 '15 at 21:52
  • @ethan I'm indeed using serverside (PHP). I can do it there but I preferred to directly obtain the results with a query. PhillipD Nice idea, I'll give it a try. Thank you both. – sms May 22 '15 at 21:54

1 Answers1

2

All you need to do is use the query you have, as the input to another query, and then use a mysql variable to hold the cumulative sum - lets say you alias your year/month string as d, and your count value as c:

select d , @sum := @sum + c as rolling_total from (
  SELECT CONCAT_WS('/',Month(created),YEAR(created)) d, Count(*) c
    FROM users
    GROUP BY Month(created),YEAR(created)
    ORDER BY Month(created) ASC ,YEAR(created) ASC
  ) q cross join (select @sum := 0) qq

demo here

pala_
  • 8,901
  • 1
  • 15
  • 32