How to create a running balance column based on account number?

Question

I have a stored procedure that returns account statement for one or multiple accounts Here is the part I have issues with: returning a running balance in each record based on account number. My query doesn't reset the balance for each account, how do I do this??

  SELECT 
    acc_account_transactions.account_no,
      COALESCE(debit_balance) as debits
     , COALESCE(credit_balance) as  credits
     , (@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
  FROM acc_account_transactions
  JOIN (SELECT @running_balance := 0) r
  ORDER BY account_no

When running this, I get something like this, but the balance doesn't reset on each account

account  debit           credit             balance
13  |2578.19        | 0.00      |  2578.19
13  |1500.00        | 0.00      |  4078.19
13  |1500000.00     | 0.00      |  1504078.19
13  |1500.00        | 0.00      |  1505578.19
14  |1500000.00     | 0.00      |  3005578.19  <--- reset before this
14  |0.00           | 13500.00  |  2992078.19
14  |0.00           | 13500.00  |  2978578.19
14  |33458.43       | 0.00      |  3012036.62
15  |0.00           | 100000.00 |  2912036.62  <--- reset before this
15  |18506.34       | 0.00      |  2930542.96
16  |946.20         | 0.00      |  2931489.16  <--- reset before this
17  |90364.77       | 0.00      |  3021853.93  <--- reset before this
21  |0.00           | 0.00      |  3021853.93  <--- reset before this
22  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |0.00           | 0.00      |  3021853.93  <--- reset before this
23  |1105500.00     | 0.00      |  4127353.93
24  |1327.00        | 0.00      |  4128680.93  <--- reset before this
25  |0.00           | 0.00      |  4128680.93  <--- reset before this

Thanks

how do I do this?? Use one more variable which stores account value for previous row. PS. What is your MySQl version precisely? — Akina, May 30, 2020 at 20:39
@Akina, thanks for responding. Can you clarify? Its "10.4.11-MariaDB - mariadb.org binary distribution" — Abeer Sul, May 30, 2020 at 20:47
Its "10.4.11-MariaDB - mariadb.org binary distribution" You may use window functions ! why do you use ancient techniques based on UDVs ? — Akina, May 30, 2020 at 23:24
In future, could you please provide your table structures as DDL (CREATE TABLE foo....) and your data as DML (INSERT INTO foo VALUES (...)) - as a courtesy to those trying to assist - help us to help you! 8-) You could also easily do this as a fiddle - but put DML and DDL here also! — Vérace, May 31, 2020 at 9:38

Rick James · Accepted Answer · 2020-06-03 19:12:42Z

4

It seems you need in simple

SELECT *, SUM(debit_balance - credit_balance)
             OVER (PARTITION BY account_no ORDER BY id) balance
FROM acc_account_transactions

fiddle

edited Jun 3, 2020 at 19:12

Rick James

74.9k5 gold badges44 silver badges105 bronze badges

answered May 30, 2020 at 23:23

Akina

18.9k2 gold badges14 silver badges19 bronze badges

My answer looks like an encyclopedia. Your's is a sentence. You got a big +1
– RolandoMySQLDBA
May 30, 2020 at 23:50
Thanks a lot! it never worked with me like this but I'm glad there is a short solution for this. And the fiddle website is great
– Abeer Sul
May 31, 2020 at 20:15

Add a comment |

RolandoMySQLDBA · Accepted Answer · 2020-05-30 22:05:29Z

Your query was only missing the ability to reset @running_balance. You can only reset @running_balance when the row has a different account_no from the previous row.

With this in mind, I give you (drum roll please) ...

PROPOSED QUERY

SELECT account_no,debits,credits,balance FROM
(SELECT 
   account_no
  ,COALESCE(debit_balance)  as debits
  ,COALESCE(credit_balance) as credits
  ,(@running_balance := IF(@curr_account_no < account_no,         0,@running_balance)) prev_runnng_bal
  ,(@curr_account_no := IF(@curr_account_no < account_no,account_no,@curr_account_no)) curr_account_no
  ,(@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
FROM acc_account_transactions,(SELECT @running_balance := 0,@curr_account_no := 0) r
ORDER BY account_no) A;

SUBQUERY WITHIN PROPOSED QUERY

SELECT 
   account_no
  ,COALESCE(debit_balance)  as debits
  ,COALESCE(credit_balance) as credits
  ,(@running_balance := IF(@curr_account_no < account_no,         0,@running_balance)) prev_runnng_bal
  ,(@curr_account_no := IF(@curr_account_no < account_no,account_no,@curr_account_no)) curr_account_no
  ,(@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
FROM acc_account_transactions,
     (SELECT @running_balance := 0,@curr_account_no := 0) r
ORDER BY account_no;

SAMPLE DATA

DROP DATABASE IF EXISTS abeersul;
CREATE DATABASE abeersul;
USE abeersul
CREATE TABLE acc_account_transactions
(
    id             INT NOT NULL AUTO_INCREMENT,
    account_no     INT NOT NULL,
    debit_balance  DOUBLE NOT NULL,
    credit_balance DOUBLE NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO acc_account_transactions
(account_no,debit_balance,credit_balance) VALUES
(13,2578.19        , 0.00     ),
(13,1500.00        , 0.00     ),
(13,1500000.00     , 0.00     ),
(13,1500.00        , 0.00     ),
(14,1500000.00     , 0.00     ),
(14,0.00           , 13500.00 ),
(14,0.00           , 13500.00 ),
(14,33458.43       , 0.00     ),
(15,0.00           , 100000.00),
(15,18506.34       , 0.00     ),
(16,946.20         , 0.00     ),
(17,90364.77       , 0.00     ),
(21,0.00           , 0.00     ),
(22,0.00           , 0.00     ),
(23,0.00           , 0.00     ),
(23,1105500.00     , 0.00     ),
(24,1327.00        , 0.00     ),
(25,0.00           , 0.00     );
SELECT * FROM acc_account_transactions;

SAMPLE DATA LOADED

mysql> DROP DATABASE IF EXISTS abeersul;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE abeersul;
Query OK, 1 row affected (0.00 sec)

mysql> USE abeersul
Database changed
mysql> CREATE TABLE acc_account_transactions
    -> (
    ->     id             INT NOT NULL AUTO_INCREMENT,
    ->     account_no     INT NOT NULL,
    ->     debit_balance  DOUBLE NOT NULL,
    ->     credit_balance DOUBLE NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO acc_account_transactions
    -> (account_no,debit_balance,credit_balance) VALUES
    -> (13,2578.19        , 0.00     ),
    -> (13,1500.00        , 0.00     ),
    -> (13,1500000.00     , 0.00     ),
    -> (13,1500.00        , 0.00     ),
    -> (14,1500000.00     , 0.00     ),
    -> (14,0.00           , 13500.00 ),
    -> (14,0.00           , 13500.00 ),
    -> (14,33458.43       , 0.00     ),
    -> (15,0.00           , 100000.00),
    -> (15,18506.34       , 0.00     ),
    -> (16,946.20         , 0.00     ),
    -> (17,90364.77       , 0.00     ),
    -> (21,0.00           , 0.00     ),
    -> (22,0.00           , 0.00     ),
    -> (23,0.00           , 0.00     ),
    -> (23,1105500.00     , 0.00     ),
    -> (24,1327.00        , 0.00     ),
    -> (25,0.00           , 0.00     );
Query OK, 18 rows affected (0.00 sec)
Records: 18  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM acc_account_transactions;
+----+------------+---------------+----------------+
| id | account_no | debit_balance | credit_balance |
+----+------------+---------------+----------------+
|  1 |         13 |       2578.19 |              0 |
|  2 |         13 |          1500 |              0 |
|  3 |         13 |       1500000 |              0 |
|  4 |         13 |          1500 |              0 |
|  5 |         14 |       1500000 |              0 |
|  6 |         14 |             0 |          13500 |
|  7 |         14 |             0 |          13500 |
|  8 |         14 |      33458.43 |              0 |
|  9 |         15 |             0 |         100000 |
| 10 |         15 |      18506.34 |              0 |
| 11 |         16 |         946.2 |              0 |
| 12 |         17 |      90364.77 |              0 |
| 13 |         21 |             0 |              0 |
| 14 |         22 |             0 |              0 |
| 15 |         23 |             0 |              0 |
| 16 |         23 |       1105500 |              0 |
| 17 |         24 |          1327 |              0 |
| 18 |         25 |             0 |              0 |
+----+------------+---------------+----------------+
18 rows in set (0.00 sec)

mysql>

SUBQUERY WITHIN PROPOSED QUERY EXECUTED

mysql> SELECT
    ->    account_no
    ->   ,COALESCE(debit_balance)  as debits
    ->   ,COALESCE(credit_balance) as credits
    ->   ,(@running_balance := IF(@curr_account_no < account_no,         0,@running_balance)) prev_runnng_bal
    ->   ,(@curr_account_no := IF(@curr_account_no < account_no,account_no,@curr_account_no)) curr_account_no
    ->   ,(@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
    -> FROM acc_account_transactions,
    ->      (SELECT @running_balance := 0,@curr_account_no := 0) r
    -> ORDER BY account_no;
+------------+----------+---------+-----------------+-----------------+------------+
| account_no | debits   | credits | prev_runnng_bal | curr_account_no | balance    |
+------------+----------+---------+-----------------+-----------------+------------+
|         13 |     1500 |       0 | 0               | 13              |       1500 |
|         13 |  1500000 |       0 | 1500            | 13              |    1501500 |
|         13 |     1500 |       0 | 1501500         | 13              |    1503000 |
|         13 |  2578.19 |       0 | 1503000         | 13              | 1505578.19 |
|         14 |  1500000 |       0 | 0               | 14              |    1500000 |
|         14 |        0 |   13500 | 1500000         | 14              |    1486500 |
|         14 |        0 |   13500 | 1486500         | 14              |    1473000 |
|         14 | 33458.43 |       0 | 1473000         | 14              | 1506458.43 |
|         15 | 18506.34 |       0 | 0               | 15              |   18506.34 |
|         15 |        0 |  100000 | 18506.34        | 15              |  -81493.66 |
|         16 |    946.2 |       0 | 0               | 16              |      946.2 |
|         17 | 90364.77 |       0 | 0               | 17              |   90364.77 |
|         21 |        0 |       0 | 0               | 21              |          0 |
|         22 |        0 |       0 | 0               | 22              |          0 |
|         23 |        0 |       0 | 0               | 23              |          0 |
|         23 |  1105500 |       0 | 0               | 23              |    1105500 |
|         24 |     1327 |       0 | 0               | 24              |       1327 |
|         25 |        0 |       0 | 0               | 25              |          0 |
+------------+----------+---------+-----------------+-----------------+------------+
18 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT account_no,debits,credits,balance FROM
    -> (SELECT
    ->    account_no
    ->   ,COALESCE(debit_balance)  as debits
    ->   ,COALESCE(credit_balance) as credits
    ->   ,(@running_balance := IF(@curr_account_no < account_no,         0,@running_balance)) prev_runnng_bal
    ->   ,(@curr_account_no := IF(@curr_account_no < account_no,account_no,@curr_account_no)) curr_account_no
    ->   ,(@running_balance := @running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) as balance
    -> FROM acc_account_transactions,(SELECT @running_balance := 0,@curr_account_no := 0) r
    -> ORDER BY account_no) A;
+------------+----------+---------+------------+
| account_no | debits   | credits | balance    |
+------------+----------+---------+------------+
|         13 |     1500 |       0 |       1500 |
|         13 |  1500000 |       0 |    1501500 |
|         13 |     1500 |       0 |    1503000 |
|         13 |  2578.19 |       0 | 1505578.19 |
|         14 |  1500000 |       0 |    1500000 |
|         14 |        0 |   13500 |    1486500 |
|         14 |        0 |   13500 |    1473000 |
|         14 | 33458.43 |       0 | 1506458.43 |
|         15 | 18506.34 |       0 |   18506.34 |
|         15 |        0 |  100000 |  -81493.66 |
|         16 |    946.2 |       0 |      946.2 |
|         17 | 90364.77 |       0 |   90364.77 |
|         21 |        0 |       0 |          0 |
|         22 |        0 |       0 |          0 |
|         23 |        0 |       0 |          0 |
|         23 |  1105500 |       0 |    1105500 |
|         24 |     1327 |       0 |       1327 |
|         25 |        0 |       0 |          0 |
+------------+----------+---------+------------+
18 rows in set (0.01 sec)

mysql>

GIVE IT A TRY !!!

HEADS UP !!! This type of SQL that uses variables for running totals will not work in MySQL 8.0. I just mentioned this yesterday (dba.stackexchange.com/questions/268148/…) — RolandoMySQLDBA, May 30, 2020 at 22:14
thanks a lot, your answer is very informative. But I had to choose the short answer, thanks again — Abeer Sul, May 31, 2020 at 20:16

Stack Exchange Network

How to create a running balance column based on account number?

2 Answers 2

PROPOSED QUERY

SUBQUERY WITHIN PROPOSED QUERY

SAMPLE DATA

SAMPLE DATA LOADED

SUBQUERY WITHIN PROPOSED QUERY EXECUTED

PROPOSED QUERY EXECUTED

GIVE IT A TRY !!!

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
stored-procedures
phpmyadmin
sum
running-totals
or ask your own question.

Linked

Hot Network Questions

How to create a running balance column based on account number?

2 Answers 2

PROPOSED QUERY

SUBQUERY WITHIN PROPOSED QUERY

SAMPLE DATA

SAMPLE DATA LOADED

SUBQUERY WITHIN PROPOSED QUERY EXECUTED

PROPOSED QUERY EXECUTED

GIVE IT A TRY !!!

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlstored-proceduresphpmyadminsumrunning-totals or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
stored-procedures
phpmyadmin
sum
running-totals
or ask your own question.