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.
;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.
;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;