gameOne gameOne - 11 months ago 43
MySQL Question

MySQL User Defined Variables Returns wrong result when used with COUNT

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 Source

You should embeed your GROUP BY query in a subquery to make your running total on final results and not while the counts are "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;