How to Calculate Running Total in MySQL

Last updated on May 17th, 2020 at 06:24 am

MySQL doesn’t have a function for calculating cumulative total, also known as running total. You need to write an SQL query to calculate cumulative sum in MySQL. So let’s look at an SQL query to calculate running total in MySQL.

How to Calculate Running Total in MySQL

Here’s how to calculate running total in MySQL. Let’s say you have the following table that contains daily sales.

mysql> create table sales(order_date date,sale int);

mysql> insert into sales values('2020-01-01',20),
('2020-01-02',25),('2020-01-03',15),('2020-01-04',30),('2020-01-05',20);

mysql> select * from sales;
+------------+------+
| order_date | sale |
+------------+------+
| 2020-01-01 |   20 |
| 2020-01-02 |   25 |
| 2020-01-03 |   15 |
| 2020-01-04 |   30 |
| 2020-01-05 |   20 |
+------------+------+

Let’s say you want to calculate running total, for this table, like so.

+------------+------+-----------------+
| order_date | sale | cumulative_sale |
+------------+------+-----------------+
| 2020-01-01 |   20 |              20 |
| 2020-01-02 |   25 |              45 |
| 2020-01-03 |   15 |              60 |
| 2020-01-04 |   30 |              90 |
| 2020-01-05 |   20 |             110 |
+------------+------+-----------------+

Bonus Read : How to Concatenate Multiple Rows into One Field in MySQL

Here’s the SQL query to calculate running total in MySQL.

mysql> set @csum := 0;
mysql> select order_date,sale, (@csum := @csum + sale) as cumulative_sale
       from sales
       order by order_date;
+------------+------+-----------------+
| order_date | sale | cumulative_sale |
+------------+------+-----------------+
| 2020-01-01 |   20 |              20 |
| 2020-01-02 |   25 |              45 |
| 2020-01-03 |   15 |              60 |
| 2020-01-04 |   30 |              90 |
| 2020-01-05 |   20 |             110 |
+------------+------+-----------------+

In the above query, we first set a user-defined variable csum to 0. The we use it to store each row’s running total. It is important to note that csum is not stored in any table. It is simply used to display the running total values.

If you want to store this running total, you can add a new column to your table and use the UPDATE clause to store running total values.

mysql> alter table sales add column cumulative_sum int;

mysql> set @csum := 0;

mysql> update sales
       set cumulative_sum = (@csum := @csum + sale)
       order by order_date;

mysql> select order_date,sale,cumulative_sum from sales;
+------------+------+----------------+
| order_date | sale | cumulative_sum |
+------------+------+----------------+
| 2020-01-01 |   20 |             20 |
| 2020-01-02 |   25 |             45 |
| 2020-01-03 |   15 |             60 |
| 2020-01-04 |   30 |             90 |
| 2020-01-05 |   20 |            110 |
+------------+------+----------------+

Bonus Read : How to Add Total Row in MySQL

You can also customize your SQL query using WHERE clause or JOINS, as shown,

mysql> set @csum := 0;
mysql> select order_date,sale, (@csum := @csum + sale) as cumulative_sale
       from sales where <condition>
       order by order_date;

Since MySQL doesn’t offer any built-in function for running total, you can use the above SQL query to running total in MySQL. You can also use MONTH(order_date) instead of order_date above if you want to write SQL for cumulative sum by month.

Once you calculate running total, you can use a charting tool to plot this data in a line chart and share it with your team. Here’s an example of a line chart that shows running total, created using Ubiq.