1

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

5
  • 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 '20 at 20:39
  • @Akina, thanks for responding. Can you clarify? Its "10.4.11-MariaDB - mariadb.org binary distribution" – Abeer Sul May 30 '20 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 '20 at 23:24
  • 1
    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 '20 at 9:38
  • @Vérace thanks for the tip, will do inshallah! – Abeer Sul May 31 '20 at 20:17
3

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

2
  • My answer looks like an encyclopedia. Your's is a sentence. You got a big +1 – RolandoMySQLDBA May 30 '20 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 '20 at 20:15
1

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

2
  • 1
    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 '20 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 '20 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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