fetching cumulative sum grouped by month on time interval

Question

This the users table. from where i am fetching data and filling in the missing months by adding table d to query.

---------------------------------------
|   uid   |       date_register        |
---------------------------------------
| 1       |      2011-07-20 02:24:36   |
---------------------------------------
| 2       |      2012-10-03 07:37:43   |
---------------------------------------
|   ...   |      ... ... ... ... ...   |
---------------------------------------
| 300000  |      2015-12-19 04:13:51   |
---------------------------------------

I want to fetch cumulative sum grouped by month. I am using following query to do this.

SELECT d.y, d.m, p.cum_sum 
            FROM 
            (SELECT a.y, b.m 
            FROM (SELECT 2015 AS Y UNION ALL SELECT 2014 AS Y) a
            CROSS JOIN (SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 AS m UNION ALL SELECT 4 AS m UNION ALL SELECT 5 AS m UNION ALL SELECT 6 AS m UNION ALL SELECT 7 AS m UNION ALL SELECT 8 AS m UNION ALL SELECT 9 AS m UNION ALL SELECT 10 AS m UNION ALL SELECT 11 AS m UNION ALL SELECT 12 AS m) b
            WHERE CONCAT(a.y, '-', b.m, '-01') BETWEEN CURDATE() - INTERVAL 13 MONTH AND CURDATE()
            ORDER BY 1, 2) d
            LEFT OUTER JOIN
            (SELECT year1,
                    month, 
                    @cnt := @cnt + total cum_sum
  FROM (
        SELECT YEAR(date_register) year1,
               MONTH(date_register) month,
               COUNT(*) total
               FROM users
               WHERE date_register >= DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL 1 YEAR
               AND useractivated = 1
               GROUP BY YEAR(date_register), MONTH(date_register)
       ) n, (SELECT @cnt := count(*) from users
 where date_register< DATE_FORMAT(NOW() ,'%Y-%m-01') - INTERVAL 1 YEAR AND useractivated = 1) u)
            p ON d.m=p.month AND d.y=p.year1
            ORDER BY 1 ASC, 2 ASC

but it returns null value if there is no transactions in that particular month. like following (sample data)

+------+-------+---------+
|   y  |   m   | cum_sum |
+------+-------+---------+
| 2014 |   10  |  12356  |
| 2014 |   11  |  13567  |
| 2014 |   12  |  14239  |
| 2015 |   01  |  15234  |
| 2015 |   02  |  16571  |
| 2015 |   03  |   NULL  |
| 2015 |   04  |  24239  |
| 2015 |   05  |  34239  |
| 2015 |   06  |  44239  |
| 2015 |   07  |  45239  |
| 2015 |   08  |  46239  |
| 2015 |   09  |  57239  |
| 2015 |   10  |  67239  |
+------+-------+---------+

so there was no transaction in march 2015, so it returning null but it should return 16571 like previous row. What I am doing wrong? Thanks.

Answer 1

Try this:

    select a.y, b.m, 
         (
          SELECT count(*) as cum_count
          FROM users
          WHERE (YEAR(date_register) = a.y and MONTH(date_register) <= b.m) or year(date_register) < a.y
         ) as cum_count 
    from
    (
    SELECT 2015 AS Y UNION ALL SELECT 2014 AS Y
    ) a
    CROSS JOIN 
    (
    SELECT 1 AS m UNION ALL SELECT 2 AS m UNION ALL SELECT 3 AS m UNION ALL SELECT 4 AS m UNION ALL SELECT 5 AS m UNION ALL SELECT 6 AS m UNION ALL SELECT 7 AS m UNION ALL SELECT 8 AS m UNION ALL SELECT 9 AS m UNION ALL SELECT 10 AS m UNION ALL SELECT 11 AS m UNION ALL SELECT 12 AS m
    ) b
    order by a.y, b.m