Retrieve MySQL Running Totals With Subqueries, Windowed Functions and User-Defined Variables

Published on December 23, 2020•Updated on November 21, 2023

Introduction

In MySQL, a running total is a derived column that computes one or more columns' values while continuously taking care of newly inserted table records. The rolling sum is useful for reporting purposes. For instance, you may use it to compute account balances for clients in a bank application or track inventory's moving balances in a store. This guide described how to generate the running totals of a customer's account balance in MySQL with sub-queries, windowed functions, and user-defined variables.

Prerequisites

Before proceeding, make sure you have the following:

An Ubuntu 20.04 server
A sudo user
A MySQL server

1. Create a Test Database

$ sudo mysql -u root -p

Enter the root password of your MySQL database and type Enter to proceed.

Create a test_bank database.

mysql> CREATE DATABASE test_bank;

Select the test_bank database.

mysql> USE test_bank;

Create a transactions table with 5 columns.

The transaction_id column uniquely identifies each transaction in the table.
The transaction_date field holds the actual transaction date.
Because you might have multiple clients's accounts, add an account_id column to identify each customer's account.
The debit column represents money deposited into the account.

The credit field records money withdrawn out from the client's account.

  mysql> CREATE table transactions
         (
         transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
         transaction_date DATE,
         account_id BIGINT,
         debit DECIMAL(17, 2),
         credit DECIMAL(17, 2)
        ) ENGINE = InnoDB;

Insert some records into the transactions table. This guide inserts records for a single customer's account. To allow a rich computation for the running total, use different dates for the transactions from January to December 2020. Don't insert any value for the transaction_id column. This field is auto-incremented because it is defined with the AUTO_INCREMENT keyword.

Populate the transactions table with the following commands.

mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-01-01', 101010, 0, 5000);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-02-01', 101010, 2000, 0);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-03-01', 101010, 0, 1000);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-04-01', 101010, 500, 0);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-05-01', 101010, 0, 6000);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-06-01', 202020, 100, 0);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-07-01', 101010, 0, 400);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-08-01', 101010, 0, 5000);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-09-01', 101010, 3000, 0);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-10-01', 101010, 0, 400);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-11-01', 101010, 0, 600);
mysql> INSERT INTO transactions(transaction_date, account_id, debit, credit) VALUES ('2020-12-01', 101010, 0, 4000);

Verify the records by querying the transactions table.

mysql> SELECT
       transaction_id,
       transaction_date,
       account_id,
       debit,
       credit    
       FROM transactions;

This query produces the following output.

+----------------+------------------+------------+---------+---------+
| transaction_id | transaction_date | account_id | debit   | credit  |
+----------------+------------------+------------+---------+---------+
|              1 | 2020-01-01       |     101010 |    0.00 | 5000.00 |
|              2 | 2020-02-01       |     101010 | 2000.00 |    0.00 |
|              3 | 2020-03-01       |     101010 |    0.00 | 1000.00 |
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 |
|              5 | 2020-05-01       |     101010 |    0.00 | 6000.00 |
|              6 | 2020-06-01       |     202020 |  100.00 |    0.00 |
|              7 | 2020-07-01       |     101010 |    0.00 |  400.00 |
|              8 | 2020-08-01       |     101010 |    0.00 | 5000.00 |
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 |
|             11 | 2020-11-01       |     101010 |    0.00 |  600.00 |
|             12 | 2020-12-01       |     101010 |    0.00 | 4000.00 |
+----------------+------------------+------------+---------+---------+
12 rows in set (0.00 sec)

2. Create a Running Total Statement

As you can see from the output you've generated in Step 1, the transaction table's raw data is incomplete. When generating a report for the customers' accounts, they may want to view a balance column in their periodic statement report. While you might consider adding the column manually in the table and update the values when inserting new records, this may not be the optimal solution because the running totals are inaccurate if records are deleted. You might fix this problem with a database trigger, but this calls for more coding and makes your database maintenance hard.

The best method of generating the running total is using user-defined variables and windowed functions. These methods derive the rolling sum column without being affected by new inserts or deletions.

To create the running balance column and display it along with the other fields, type the SQL command below.

mysql> SELECT
       transaction_id,
       transaction_date,
       account_id,
       debit,
       credit,          
       cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance
       FROM transactions
       JOIN (SELECT @balance := 0) as tmp
       ORDER BY transaction_date ASC, transaction_id ASC 
       ;

This query produces the following output.

+----------------+------------------+------------+---------+---------+----------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance  |
+----------------+------------------+------------+---------+---------+----------+
|              1 | 2020-01-01       |     101010 |    0.00 | 5000.00 |  5000.00 |
|              2 | 2020-02-01       |     101010 | 2000.00 |    0.00 |  3000.00 |
|              3 | 2020-03-01       |     101010 |    0.00 | 1000.00 |  4000.00 |
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 |  3500.00 |
|              5 | 2020-05-01       |     101010 |    0.00 | 6000.00 |  9500.00 |
|              6 | 2020-06-01       |     202020 |  100.00 |    0.00 |  9400.00 |
|              7 | 2020-07-01       |     101010 |    0.00 |  400.00 |  9800.00 |
|              8 | 2020-08-01       |     101010 |    0.00 | 5000.00 | 14800.00 |
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 | 11800.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 | 12200.00 |
|             11 | 2020-11-01       |     101010 |    0.00 |  600.00 | 12800.00 |
|             12 | 2020-12-01       |     101010 |    0.00 | 4000.00 | 16800.00 |
+----------------+------------------+------------+---------+---------+----------+
12 rows in set, 2 warnings (0.03 sec)

As you can see, the information generated above is now valuable to the account holders. They can now get a better insight into how their account balance changed during the period.

The formula of the running total is:

RUNNING TOTAL = PREVIOUS DAYS RUNNING BALANCE + CURRENT DAY CREDIT(MONEY IN) - CURRENT DAY DEBIT(MONEY OUT)

The (SELECT @balance := 0 join statement initializes the @balance variable to a zero value while the cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance increments the value of the running total balance column for each record.

Please note when using MySQL 8.x and later versions, you might get a warning as shown above('12 rows in set, 2 warnings (0.03 sec)), which you can further examine by running the SHOW WARNINGS` command.

mysql> SHOW WARNINGS;
...                                                                                    |

| Warning | 1287 | 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)'. |
...

To get the result above without any warnings in MySQL version 8.x and above, use the windowed SUM() expression and change the query to the format shown below.

mysql> SELECT
       transaction_id,
       transaction_date,
       account_id,
       debit,
       credit,          
       SUM(credit - debit) over (ORDER BY transaction_date ASC, transaction_id ASC) as balance
       FROM transactions
       ;

The query above should generate the same results without any warnings:

+----------------+------------------+------------+---------+---------+----------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance  |
+----------------+------------------+------------+---------+---------+----------+
|              1 | 2020-01-01       |     101010 |    0.00 | 5000.00 |  5000.00 |
|              2 | 2020-02-01       |     101010 | 2000.00 |    0.00 |  3000.00 |
|              3 | 2020-03-01       |     101010 |    0.00 | 1000.00 |  4000.00 |
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 |  3500.00 |
|              5 | 2020-05-01       |     101010 |    0.00 | 6000.00 |  9500.00 |
|              6 | 2020-06-01       |     202020 |  100.00 |    0.00 |  9400.00 |
|              7 | 2020-07-01       |     101010 |    0.00 |  400.00 |  9800.00 |
|              8 | 2020-08-01       |     101010 |    0.00 | 5000.00 | 14800.00 |
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 | 11800.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 | 12200.00 |
|             11 | 2020-11-01       |     101010 |    0.00 |  600.00 | 12800.00 |
|             12 | 2020-12-01       |     101010 |    0.00 | 4000.00 | 16800.00 |
+----------------+------------------+------------+---------+---------+----------+
12 rows in set (0.00 sec)

Irrespective of whether you're using the user-defined variables or the windowed SUM() function, you must order the records chronologically, by the transaction_date field, in ascending order.

An account holder can make multiple transactions in a production environment in a single day, which requires arranging the transactions by the transaction_id column. The clause for ordering the records as they are summed up should be ... ORDER BY transaction_date ASC, transaction_id ASC.

3. Filter Records

It's possible to filter a running total and still keep the balance column intact. To do this, you need to use a MySQL sub-query clause. For instance, to retrieve the customer's transactions for the period between 1st April 2020 to 1st October 2020, use the syntax below.

MySQL 5.x and below.

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance
           FROM transactions
           JOIN (SELECT @balance := 0) as tmp
           ORDER BY transaction_date ASC, transaction_id ASC
       ) as tmp_2 
       where transaction_date between '2020-04-01' and '2020-10-01'
       ;

This query produces the following output.

+----------------+------------------+------------+---------+---------+----------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance  |
+----------------+------------------+------------+---------+---------+----------+
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 |  3500.00 |
|              5 | 2020-05-01       |     101010 |    0.00 | 6000.00 |  9500.00 |
|              6 | 2020-06-01       |     202020 |  100.00 |    0.00 |  9400.00 |
|              7 | 2020-07-01       |     101010 |    0.00 |  400.00 |  9800.00 |
|              8 | 2020-08-01       |     101010 |    0.00 | 5000.00 | 14800.00 |
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 | 11800.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 | 12200.00 |
+----------------+------------------+------------+---------+---------+----------+
7 rows in set, 2 warnings (0.00 sec)

MySQL 8.x and above:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           SUM(credit - debit) over (ORDER BY transaction_date ASC, transaction_id ASC ) as balance
           FROM transactions
       ) AS tmp_2
       where transaction_date between '2020-04-01' and '2020-10-01' 
       ;

This query produces the following output.

+----------------+------------------+------------+---------+---------+----------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance  |
+----------------+------------------+------------+---------+---------+----------+
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 |  3500.00 |
|              5 | 2020-05-01       |     101010 |    0.00 | 6000.00 |  9500.00 |
|              6 | 2020-06-01       |     202020 |  100.00 |    0.00 |  9400.00 |
|              7 | 2020-07-01       |     101010 |    0.00 |  400.00 |  9800.00 |
|              8 | 2020-08-01       |     101010 |    0.00 | 5000.00 | 14800.00 |
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 | 11800.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 | 12200.00 |
+----------------+------------------+------------+---------+---------+----------+
7 rows in set (0.00 sec)

4. Page Records

Sometimes you need to limit the number of rows per query and return "pages" of records. To page records and still show the running total balance column, use the LIMIT clause alongside a sub-query.

Run the query below to display page 1:

MySQL 8.x and above:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           SUM(credit - debit) over (ORDER BY transaction_date ASC, transaction_id ASC ) as balance
           FROM transactions
       ) AS tmp_2
       limit 0, 4
       ;

MySQL 5.x and below:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance
           FROM transactions
           JOIN (SELECT @balance := 0) as tmp
           ORDER BY transaction_date ASC, transaction_id ASC
       ) AS tmp_2
       limit 0, 4
       ;

Output of page 1:

+----------------+------------------+------------+---------+---------+---------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance |
+----------------+------------------+------------+---------+---------+---------+
|              1 | 2020-01-01       |     101010 |    0.00 | 5000.00 | 5000.00 |
|              2 | 2020-02-01       |     101010 | 2000.00 |    0.00 | 3000.00 |
|              3 | 2020-03-01       |     101010 |    0.00 | 1000.00 | 4000.00 |
|              4 | 2020-04-01       |     101010 |  500.00 |    0.00 | 3500.00 |
+----------------+------------------+------------+---------+---------+---------+
4 rows in set (0.00 sec)

Run the query below to display page 2:

MySQL 8.x and above:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           SUM(credit - debit) over (ORDER BY transaction_date ASC, transaction_id ASC ) as balance
           FROM transactions
       ) AS tmp_2
       limit 4, 4
       ;

MySQL 5.x and below:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance
           FROM transactions
           JOIN (SELECT @balance := 0) as tmp
           ORDER BY transaction_date ASC, transaction_id ASC
       ) AS tmp_2
       limit 4, 4
       ;

Output of page 2:

+----------------+------------------+------------+--------+---------+----------+
| transaction_id | transaction_date | account_id | debit  | credit  | balance  |
+----------------+------------------+------------+--------+---------+----------+
|              5 | 2020-05-01       |     101010 |   0.00 | 6000.00 |  9500.00 |
|              6 | 2020-06-01       |     202020 | 100.00 |    0.00 |  9400.00 |
|              7 | 2020-07-01       |     101010 |   0.00 |  400.00 |  9800.00 |
|              8 | 2020-08-01       |     101010 |   0.00 | 5000.00 | 14800.00 |
+----------------+------------------+------------+--------+---------+----------+
4 rows in set (0.00 sec)

Run the query below to display page 3:

MySQL 8.x and above:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           SUM(credit - debit) over (ORDER BY transaction_date ASC, transaction_id ASC ) as balance
           FROM transactions
       ) AS tmp_2
       limit 8, 4
       ;

MySQL 5.x and below:

mysql> SELECT * FROM
       (
           SELECT
           transaction_id,
           transaction_date,
           account_id,
           debit,
           credit,          
           cast((@balance := @balance + credit - debit) as decimal(16, 2)) as balance
           FROM transactions
           JOIN (SELECT @balance := 0) as tmp
           ORDER BY transaction_date ASC, transaction_id ASC
       ) AS tmp_2
       limit 8, 4
       ;

Output of page 3.

+----------------+------------------+------------+---------+---------+----------+
| transaction_id | transaction_date | account_id | debit   | credit  | balance  |
+----------------+------------------+------------+---------+---------+----------+
|              9 | 2020-09-01       |     101010 | 3000.00 |    0.00 | 11800.00 |
|             10 | 2020-10-01       |     101010 |    0.00 |  400.00 | 12200.00 |
|             11 | 2020-11-01       |     101010 |    0.00 |  600.00 | 12800.00 |
|             12 | 2020-12-01       |     101010 |    0.00 | 4000.00 | 16800.00 |
+----------------+------------------+------------+---------+---------+----------+
4 rows in set (0.00 sec)

The MySQL LIMIT clause takes two parameters — the offset and number of records — to limit the number of rows returned for each page. The offset is the number of records to skip. On page 1, you don't want to skip any records, so set the offset value to 0. On page 2, you must skip the four records that you displayed on page 1. On page 3, you must skip the eight records from page 1 and 2.

Conclusion

In this tutorial, you've generated a derived running total column for MySQL data using sub-queries, user-defined variables, and windowed functions. Use this guide to run cumulative balances for your data without relying on complex MySQL triggers.

Retrieve MySQL Running Totals With Subqueries, Windowed Functions and User-Defined Variables

Introduction

Prerequisites

1. Create a Test Database

2. Create a Running Total Statement

3. Filter Records

4. Page Records

Conclusion

Products

Features

Marketplace

Resources

Company