CODEWITHSUNDEEP
mysqlvariablescountdefined
gameOne
gameOne

Reputation: 619

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?

Upvotes: 0

Views: 205

Answers (2)

Thomas G
Thomas G

Reputation: 10216

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;

Upvotes: 1

Wouter
Wouter

Reputation: 1353

Read the following: http://dev.mysql.com/doc/refman/5.7/en/user-variables.html

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

Following code should work as a solution (working with subqueries):

SELECT month(from_unixtime(u.createdDate)) as month, 
       count(u.id) as monthlyusers, 
       (select count(*) from user USER where year(USER.createdDate)='2016' and month(USER.createdDate)<=month(u.createdDate)) as totalUsers
FROM user u 
where year(from_unixtime(u.createdDate)) = '2016' 
group by month(from_unixtime(u.createdDate));

Upvotes: 0

Related Questions