How to Calculate Cumulative Total in MySQL

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

Many times you may need to calculate running total or cumulative total for a specific column, such as sales. Since there is no function to calculate cumulative total in MySQL, you need to accomplish it via SQL query. Here’s an SQL query to calculate cumulative total in MySQL.

How to Calculate Cumulative Total in MySQL

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

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 cumulative sum of sales, 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 |
+------------+------+-----------------+

Here’s the SQL query to calculate cumulative sum 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 |
+------------+------+-----------------+

Bonus read : How to Create Pivot Table in MySQL

In the above query, we set a temporary variable csum to 0. Then for each row we use it to calculate and store the cumulative sum.

Please note, this cumulative sum is not stored in any column. It is simply displayed in result.

If you want to store this value, you need to add a new column to your table and use the UPDATE clause as shown below.

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 |
+------------+------+----------------+

You can customize your SQL query to include filters using WHERE clause or JOINS, as shown below

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

Bonus Read : How to Calculate Median in MySQL

Since MySQL doesn’t offer built-in function for cumulative sum, you can use the above SQL query to calculate cumulative total in MySQL.

After you can calculate cumulative total in MySQL, you can use a charting tool easily plot them as a line chart or bar graph, as shown below. Here’s an example of a chart created using Ubiq.