Results 1 to 2 of 2

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

  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
    24
    Look into Sum() OVER PARTITION syntax. For a start review https://ozanecare.com/calculate-cumu...otal-in-mysql/

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
  •