1

Good Day everyone, i Hope someone will help me. I have 1 table and i want to create a mysql query that would add a column to the last that will calculate the running balance of deposited amount and cheque issued amount (deposited amount - cheque issued amount) = RUNNING BALANCE

tbl

-------------------------------------------------------------------------
| Date_Deposited | deposited_amount | Date_Cheque_Issued | issued_amount |
-------------------------------------------------------------------------
   May 16, 2019        1,000.00          May 16, 2019          500.00
                                         May 17, 2019          100.00
   May 18 2019         1,000.00

i am expecting to create this kind of table with BALANCE column in the last

------------------------------------------------------------------------------
| Date_Deposited | deposited_amount | Date_Cheque_Issued | issued_amount | Bal |
-------------------------------------------------------------------------
May 16, 2019        1,000.00          May 16, 2019       500.00         500.00                                           
                                      May 17, 2019       100.00         400.00
May 18 2019         1,000.00                                          1,400.00
Joseph Sible-Reinstate Monica
  • 45,431
  • 5
  • 48
  • 98
Tzuy
  • 25
  • 4
  • For this, you should use a SP. In that, use cursor to process each row as the running balance depends on current row deposit and check issued amount as well as the running balance of previous row. – Manish Bansal Jun 23 '19 at 05:51
  • This is not a rare question on SO try googling your question. – P.Salmon Jun 23 '19 at 07:52

2 Answers2

0

A running balance can be calculated using a temporary table. The following query can be used:

create TEMPORARY table tbl_temp (select * from tbl);
update tbl t1 set t1.Bal=(SELECT SUM(t2.deposited_amount-t2.issued_amount) FROM tbl_temp t2 WHERE t2.id<=t1.id);
select * from tbl;

A temporary table is needed because the table that is going to be updated will be locked during the update and so it cannot be used for both update and select. Note that a temporary table only exists for the duration of the connection to the MySQL server.

The above query uses a sub query, which returns balance of all rows in the temporary table upto and including the current row. The balance is assigned to the current row in the actual table. The query assumes that the tbl table has a primary key column called 'id'.

Nadir Latif
  • 3,690
  • 1
  • 15
  • 24
  • This is wrong. Running balance depends on previous transaction. How will this work? – Manish Bansal Jun 23 '19 at 10:26
  • He does want a running balance. Look at the second row of the desired result. – Barmar Jun 25 '19 at 17:44
  • As mentioned on https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql its not possible to calculate running balance using plain sql. Its better to use Stored Procedures or a programming language – Nadir Latif Jun 26 '19 at 01:59
  • It is possible to calculate running balance using a temporary table in MySQL. I have updated the answer – Nadir Latif Jun 26 '19 at 04:53
-2

You can first alter the table to add column such as 

ALTER TABLE TableName
ADD Bal DECIMAL(40,2);

After creating a column in table you can update it with data using update statement

UPDATE TableName set Bal=(deposited_amount - issued_amount);
Subhan Mangi
  • 1
  • 1