how to create a MySql query to display running balance from credit and debit where multiple customers have indivdual balances

I have a table as with the structure below: enter image description here

The Table is a simple credit and debit table for different customers ( each customer has his own ID).

Transactions take place on different dates. Each transaction has its own ID which is chronologically generated.

A view must be created as showing the running balance of each customer. The view gives the list chronologically arranged.

The list items are

  1. Transaction:
  • ID
  • Customer_ID
  • Date
  • Credit
  • Debit and
  • Balance (Calculated)

I would like to get the query code for solving the above problem. Thanking you in anticipation.

Add Comment
1 Answer(s)

Replace the table with your actual table name and check if this what your expected result as you said it gives you the running total of each customer ordered by date.

select Transaction_ID, Customer_ID, Date, Credit, Debit, sum(Balance) over(partition by Customer_ID order by Date )  form table_name; 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.