How to get Running Balance based on credit and debit in a single column by SQL query?

How to get Running Balance based on credit and debit in a single column by SQL query

While our project development time, we all face accounting based queries issue and we all know that not all the developers are completely familiar with sql queries and advance queries. right!

Basically when we are creating a project based on banking system or any transaction system then we always want to get all the transaction through query. and then we just have to display it as it is.

Let me come to the point of the today’s article which is

How to get Running Balance based on credit and debit in a single column by SQL query?

Step by step to generate Running balance if you have only one column for credit and debit column.

So I have a table in which i have stored below data.

Table Name is: user_transaction

transaction_iduser_idamount
1410000
24-500
34300
44-6000

So here in the above table we taken 3 column in which transaction id is an auto increment id, user_id is the foreign key and the amount is for credit and debit entry by user_id 4.

So whenever if we are working on this type of work then we always care about the clear transaction and sometimes it’s complicated to handle this by coding. so we need the query which shows us the exact running balance based on all the credit and debits entry. So let’s see the SQL query.

SELECT *, (select sum(amount) from
user_transaction Tin where Tin.transaction_id<=Tout.transaction_id and user_id=4) as balance from user_transaction Tout where user_id=4 order by transaction_id desc

Explanation:

So, What we are doing in this query.

Here we are counting the amount based on Tin “Which is stands or Transaction In” and Tout “Which is stands for Transaction Out”. on condition that transaction_id of Tin should be less than equal to transaction_id of Tout where user_id is 4 and that’s it.

So run the query and if you face any issue regarding this query You can comment below.

Thank you for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *