When you have to calculate a running sum over a period of time, say calculate count of users in application month by month, user defined variables can be used to make the query simpler.
User defined variables can store a value and it can be referred in another SQL statement and they are session specific.
Below is a query to calculate month on month sum of users in an application.
SET @rsum:=0;
SELECT month, users, (@rsum := @rsum + users) as total_users
FROM ( SELECT date_format(create_date, '%Y-%m') as month,
COUNT(*) as users
FROM app_users
GROUP BY month
ORDER BY month ) as s1 ;
SET @rsum:=0;
Since user defined variables are session specific, they need to be initialized to 0 before running the same query again, hence the SET statement at the end.
Below is a sample result returned from the above query,
+---------+-------+-------------+
| month | users | total_users |
+---------+-------+-------------+
| 2016-08 | 552 | 552 |
| 2016-09 | 302 | 854 |
| 2016-10 | 467 | 1321 |
| 2016-11 | 254 | 1575 |
| 2016-12 | 545 | 2120 |
| 2017-01 | 929 | 3049 |
| 2017-02 | 164 | 3213 |
+---------+-------+-------------+
7 rows in set (0.01 sec)
References: