Cannot assign local variable using ":=" inside subquery

All we need is an easy explanation of the problem, so here it is.

I have a query which counts the total of users by month or by day. So what I have to do is to accumulate in a variable that total.
My code is:

select * from (select createdDate,newUsers, ( csum := csum + newUsers ) as totalUsers
            from (
                select IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dtCreatedDate,'%Y-%m'),dtCreatedDate) as createdDate,
                            count(dtCreatedDate) as newUsers
                         from Users u
                         group by IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dtCreatedDate,'%Y-%m'),dtCreatedDate)
                    
            ) as temp order by createdDate) as temp2
            where createdDate BETWEEN IF(
            STRCMP(@byType,'month')=0,DATE_FORMAT(dateOne,'%Y-%m'),dateOne) 
           and IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dateTwo,'%Y-%m'),dateTwo);

My problem is that I cannot use the statement csum := csum + newUsers because I get this syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= csum + newUsers)
            from (
                select IF(STRCMP(@by' at line 16

That syntax error can be avoided If I use csum = csum + newUsers and I’ve tried using csum = csum + newUsers but it seems has not effect because the csum is always 0.
I’ve also tried using select csum = csum + newUsers or select csum := csum + newUsers but none works.

And I used the selec into like this: select (csum + newUsers) into csum and I also get the next error:

Misplaced INTO clause, INTO is not allowed inside subqueries, and must be placed at end of UNION clauses.

I know that I can use a user-defined variables like @csum := @csum + newUsers but I get the next warning message:

Setting user variables within expressions is deprecated and will be removed in a future release. 
Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

So, I would like to know if there is a way I can resolve my problem without using user-defined variables

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I resolved my doubt by using the window functions. Instead of sum the user defined variable @csum I used SUM, over and order by clauses:

select * from (select createdDate,newUsers,  
                SUM(newUsers) over (order by dtCreatedDate) as totalUsers
            from (
                select IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dtCreatedDate,'%Y-%m'),dtCreatedDate) as createdDate,
                            count(dtCreatedDate) as newUsers
                         from Users  e 
                         group by IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dtCreatedDate,'%Y-%m'),dtCreatedDate)
                    
            ) as temp order by createdDate) as temp2
            where createdDate BETWEEN IF(
            STRCMP(@byType,'month')=0,DATE_FORMAT(dateOne,'%Y-%m'),dateOne) 
           and IF(STRCMP(@byType,'month')=0,DATE_FORMAT(dateTwo,'%Y-%m'),dateTwo);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply