Click here to Skip to main content
15,905,028 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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| 1400.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.


What I have tried:

mysql query
SELECT * ,(SELECT SUM(tutar) from customer_hesap WHERE id<=cHes.id) as YURUYEN_TOPLAM FROM `customer_hesap` as cHes
Posted
Updated 14-Sep-22 23:46pm
Comments
[no name] 14-Sep-22 21:11pm    
Nobody uses a running balance. There's a total for the period (month, quarter, and / or year) and a "current balance", and that's about it. Debit / credit columns can be created with a CASE off the original column.
CHill60 15-Sep-22 6:47am    
"Nobody uses a running balance" - I do. Amongst other things, I note the amount that was in my Accounts when specific payments were made into them and transfer that amount into savings/investments. With a running balance I can do it at a glance, but with current balance only I'd have to either calculate backwards or remember to check the account on specific days. PITA for me.
[no name] 15-Sep-22 12:21pm    
I'm talking about "business accounting" ... not managing your check book. Ask yourself, when dealing with hundreds of customers and thousands of transactions, what would a running balance (on every transaction) tell you? Particularly when virtually all billing and payment cycles are "monthly".

1 solution

You were quite close with your attempt, but you first need to split out the amount (tutar?) as either a debit or a credit. I prefer to use Common Table Expressions (see An Introduction to MySQL CTE[^]) , but you could use a sub-query or a temporary table if you prefer.
SQL
;with cte as 
(
	select id, customer_id, orders_tip, comment
	, case when orders_tip = 1 then amount else 0 end as debit
	, case when orders_tip = 0 then amount else 0 end as credit
	from @customer_hesap
)
select id, customer_id, orders_tip, comment, debit, credit
,(SELECT SUM(debit - credit) from cte WHERE id <= cHes.id) as YURUYEN_TOPLAM 
FROM cte as cHes
WHERE customer_id=25;
Note that I've used SUM(debit - credit) instead of amount. By the way, it's usually the other way around but this fits your expected results.

I'm not a fan of correlated sub-queries so I prefer to use Window function (see MySQL :: MySQL 8.0 Reference Manual :: 12.21.2 Window Function Concepts and Syntax[^] ) - I find them easier to read, but also correlated sub-queries can kill performance.
SQL
;with cte as 
(
	select id, customer_id, orders_tip, comment
	, case when orders_tip = 1 then amount else 0 end as debit
	, case when orders_tip = 0 then amount else 0 end as credit
	from @customer_hesap
)
select id, customer_id, orders_tip, comment, debit, credit
,sum(debit - credit) over (partition by customer_id order by id)
from cte a;
 
Share this answer
 
v2
Comments
Mert Baran 30-Sep-22 21:30pm    
not accepting cte statement in mysql database version I am using
CHill60 1-Oct-22 2:48am    
Then use a sub-query or a temporary table instead - as I said in the solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900