mySQL total user count

Grouping by month

I want to list the total count of registered users grouped by month

Well, the difficulty about this is that I don't want the count per month, but the the total count of users up to (and including) the month.

User table structure

+---------------+--------------+------+-------------------+----------------+
| Field         | Type         | Null | Default           | Extra          |
+---------------+--------------+------+-------------------+----------------+
| ID            | int(11)      | NO   | NULL              | auto_increment |
| email         | varchar(225) | NO   | NULL              |                |
................................-CUT-.......................................
| registered    | timestamp    | NO   | CURRENT_TIMESTAMP |                |
+---------------+--------------+------+-------------------+----------------+

Example data

1 example1@mail 2012-04-04 xx:xx:xx
2 example2@mail 2012-05-04 xx:xx:xx
3 example3@mail 2012-05-04 xx:xx:xx

Preferred output

+------+-------+-------+
| Year | Month | Count |
+------+-------+-------+
| 2012 | 01    | 0     |
| 2012 | 02    | 0     |
| 2012 | 03    | 0     |
| 2012 | 04    | 1     |
| 2012 | 05    | 3     |
+------+-------+-------+

The NULL results aren't necessary.

How could I achieve that result in pure mySQL?

• mysql
• count

I have not tried this but something along these lines should work -

SELECT tots.*, @var := @var + tots.`count`
FROM (
    SELECT
        YEAR(registered) AS `year`,
        MONTH(registered) AS `month`,
        COUNT(*) AS `count`
    FROM user
    GROUP BY `year`, `month`
) AS tots, (SELECT @var := 0) AS inc

This approach first gets the first day of the month for all months in which any registration occurred. It then joins to every user that had a registration greater than the first day of the month, and then counts the number of users.

SELECT
    YEAR(dates.first_day_of_month) AS registration_year,
    MONTH(dates.first_day_of_month) AS registration_month,
    COUNT(u.ID)
FROM (
    SELECT DISTINCT
        DATE_SUB(
            DATE_ADD(
                DATE_SUB(registered,INTERVAL (DAY(registered)-1) DAY), 
            INTERVAL 1 MONTH), 
        INTERVAL 1 SECOND) first_day_of_month
    FROM user 
) dates 
    LEFT JOIN user u ON u.registered <= dates.first_day_of_month
GROUP BY dates.first_day_of_month

If you want to avoid the gaps in months where no registrations occurred, you could substitute the sub-query with another that used a pre-existing "numbers" table to get a list of all possible months.

You can do it with a couple of user variables:

set @c = 0;
set @d = 0;
select y, m, @d := @d + Count as Count from
  (select year(registered) as y, 
     month(registered) as m, 
     @c := @c + count(*) as Count
   from user
   group by y,m) as t;

gives you

+------+------+-------+
| y    | m    | Count |
+------+------+-------+
| 2011 |    1 |  2455 | 
| 2011 |    2 | 14253 | 
| 2011 |    3 | 42311 |