Cannot assign local variable using ":=" inside subquery

Question

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

• Akina: I cannot use the statement csum := csum + newUsers because I get syntax error. csum is local variable and not user-defined variable (like @byType). LV can be assigned only using either SET or SELECT INTO in outer query, not inline or in subquery/CTE. So use UDV instead of LV.
• Edgar Magallon: I guess I will have to use UDV in this case. But I wonder how it will be possible for solving that problem in future releases when the assignment of UDV within expressions be removed.
• Akina: User-defined variabes inline usage is a palliative. Now window functions and recursive CTEs are implemented, and this old technique not needed.
• Edgar Magallon: I didn't know about that concepts but I've read a little and I will try to implement my code with those techniques. Thanks!
• Rick James: Please show us the full text of the syntax error.

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