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

by Abeer Sul   Last Updated May 30, 2020 20:06 PM

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



Related Questions


Group rows by running aggregate

Updated February 25, 2016 01:02 AM

Problem in mysql database!

Updated September 13, 2019 11:06 AM

Running Total: To store or compute

Updated April 17, 2015 21:02 PM