gameOne gameOne - 14 days ago 5
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

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;
Comments