How to query Customer's credit/debit total balance with this table structure in MYSQL

[mby4719]

I have a table in mysql. The table name is customer_transaction. This table contains current movements.
If the orders_type column is 0, the invoice has been received, if it is 1, the invoice has been sold. Now, how can I find the debit/credit/running balance in this table according to the customer id? What I want to do is something like this;

| id | customer_id|orders_tip| comment |amount |
| --- | -------------|----------| ---------------|------------|
| 1 | 25 | 0 |acc. opening | 400.25 |
| 2 | 25 | 0 |purc invoice | 1000.00 |
| 3 | 25 | 1 |salesinvoice | 600.00 |
| 4 | 25 | 1 |salesinvoice | 400.25 |
| 5 | 26 | 0 |purc invoice | 700.25 |
| 6 | 26 | 1 |salesinvoice | 900.95 |
| 7 | 27 | 1 |salesinvoice | 400.25 |
| 8 | 26 | 0 |purc invoice | 125.79 |
-----------------------------------------------------------------

for customer_id=25 ;

| id | customer_id|orders_tip| comment |debit |credit |balance |
| --- | -------------|------------| -------------|----------|---------|----------- |
| 1 | 25 | 0 |acc. opening| 0 |400.25 | -400.25 |
| 2 | 25 | 0 |purc invoice| 0 |1000.00| -1400.25|
| 3 | 25 | 1 |salesinvoice| 600.00 | 0 | -800.25 |
| 4 | 25 | 1 |salesinvoice| 1400.25 | 0 | 600.00 |

I need MYSQL query that will return the result in the table. Thank you very much in advance. I hope I explained correctly.

[June7]

Look into Sum() OVER PARTITION syntax. For a start review https://ozanecare.com/calculate-cumu...otal-in-mysql/

[johnsonmillie53]

You can use a combination of the SUM() function, the CASE statement, and a subquery to retrieve the customer's credit/debit total balance with this table structure in MySQL.

Here is an example of a MySQL query that will return the result in the table you provided:

SELECT id, customer_id, orders_tip, comment,
SUM(CASE WHEN orders_tip = 0 THEN amount ELSE 0 END) AS debit,
SUM(CASE WHEN orders_tip = 1 THEN amount ELSE 0 END) AS credit,
(SELECT SUM(amount) FROM customer_transaction WHERE customer_id = 25 AND orders_tip = 0) -
(SELECT SUM(amount) FROM customer_transaction WHERE customer_id = 25 AND orders_tip = 1) AS balance
FROM customer_transaction
WHERE customer_id = 25
GROUP BY id, customer_id, orders_tip, comment
ORDER BY id;

This query will return the customer's credit/debit total balance for customer_id = 25.

In this query, we are using the SUM() function in conjunction with the CASE statement to calculate the total debit and credit amounts for each transaction. The query first starts by selecting the specified columns from the table, then it uses the SUM() function with the CASE statement to calculate the debit and credit amounts.

Then the query is using two subqueries to calculate the total debit and credit amount for customer_id = 25. Then it is subtracting the credit amount from debit amount to get the balance.

Finally, the query is grouping the results by id, customer_id, orders_tip, comment and ordered by id to get the results in the same order as in your example.

You can change the customer_id = 25 to any other customer_id in the WHERE clause to get the result for that specific customer_id.

[Parshan]

Can i use the following code for thatdownload gb whatsapp

SELECT
  id,
  customer_id,
  orders_tip,
  comment,
  IF(orders_tip = 0, amount, 0) AS debit,
  IF(orders_tip = 1, amount, 0) AS credit,
  @balance := IF(orders_tip = 0, @balance + amount, @balance - amount) AS balance
FROM
  customer_transaction,
  (SELECT @balance := 0) AS balance_init
WHERE
  customer_id = 25
ORDER BY
  id;