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?

Thomas G · Accepted Answer · 2016-11-21 12:52:21Z

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;

edited Nov 21, 2016 at 12:52

answered Nov 21, 2016 at 11:05

Thomas G

10.3k7 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wouter · Accepted Answer · 2016-11-21 11:18:40Z

0

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

answered Nov 21, 2016 at 11:18

Wouter

1,3633 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

MySQL User Defined Variables Returns wrong result when used with COUNT

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related