Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Sep 2022
    Posts
    1

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

    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.

  2. #2
    Join Date
    Nov 2020
    Posts
    26
    Look into Sum() OVER PARTITION syntax. For a start review https://ozanecare.com/calculate-cumu...otal-in-mysql/

  3. #3
    Join Date
    Aug 2022
    Posts
    20
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •