MySQL User Defined Variables Returns wrong result when used with COUNT

Question

I want to find the number of registered users for a particular year grouped by month. Below is the query

set @numberOfUsers := 0; 
SELECT month(from_unixtime(u.createdDate)) as month, count(u.id) as monthlyusers, 
(@numberOfUsers := @numberOfUsers + count(u.id)) as totalUsers 
FROM user u 
where year(from_unixtime(u.createdDate)) = '2016' 
group by month(from_unixtime(u.createdDate));

However, I'm not getting the right result for the totalUsers, it is not adding the result of the previous rows.

month  |  monthlyUsers  |  totalUsers
10     |       1        |      1
11     |       3        |      3

The totalUsers value in the 11th month should have been 4. Not sure what is wrong in the query. Any help?

Answer 1

You should embeed your GROUP BY query in a subquery to compute your running total on definitive results and not while the counts are still "being computed" :

set @numberOfUsers := 0; 
SELECT T.*, (@numberOfUsers := @numberOfUsers + T.monthlyusers) as totalUsers 
FROM  
(
    SELECT month(from_unixtime(u.createdDate)) as month, count(u.id) as monthlyusers
    FROM user u 
    where year(from_unixtime(u.createdDate)) = '2016' 
    group by month(from_unixtime(u.createdDate))
) T;