Balance calculation problem with Foreign Exchange in Mysql

I am trying to make a current balance system for pre-accounting in mysql. I create receipts in different currencies and collect payments in different currencies. My main currency is Turkish Lira (TRY). The amount field in the table below is the amount of the foreign currency itself. I am trying to make my calculations using dovizKarsiligiTl. yes I missed it

Original table

id	meblag	dovizKuru	dovizKarsiligi	dovizID
452	-60	32,31	1938,63	2
455	100	35,14	3514	3
456	-300	1	300	1

dovizID	name
1	TRY
2	USD
3	EURO

SET @TRY_balance := 0;
SET @USD_balance := 0;
SET @EUR_balance := 0;

id	amount	foreignExchangeEquivalentTl	exchange rate	dovizID	TRY_balance	USD_balance	EUR_balance
452	-60	1938.63	32.31	2	0	1938.6	0
455	100	3514.00	35.14	3	0	1938.6	3514
456	-300	300	1	1	-300	1938.6	3514

In the table above, the TRY_BAKİYE, USD_BAKİYE, EUR_BAKİYE fields are virtual, that is, they can change depending on the transaction we perform in each row.

The table above is now my raw table and my balance fields are incorrect according to my query result.

In line 1, I owe 1938.6 TL, equivalent to -60 dollars. In the 2nd line, I paid 3514 TL for 100 euros, so according to the balance status of the 2nd line, my debt should have been completed and 1575.4 TL should have become a creditor. In the 3rd line, I owed 300 TL in return for -300 TL, and since I was credited with 1575.4 TL in the 2nd line, I should have become a creditor of 1275.4 TL. Accordingly, the following table should be formed. However, I cannot find the query that will create this table.

id	amount	exchange rate	dovizID	USD_balance	EUR_balance
452	-60	32.31	2	1938.6	0
455	100	35.14	3	0	1575.4
456	-300	1	1	0	1275.4

The data here is not fixed but variable. Payment or debt status can be understood from the -+ value in the amount. A positive TL value is kept as the foreign currency equivalent in each line.

I tried making inquiries with Chatgpt for 8 hours, but I did not get any successful results.

My incorrect query is below

SELECT
id,
meblag,
dovizKuru,
dovizKarsiligi,
dovizID,
SUM( CASE WHEN dovizID = 1 THEN CASE WHEN meblag > 0 THEN @TRY_bakiye:= @TRY_bakiye + dovizKarsiligi ELSE @TRY_bakiye := @TRY_bakiye -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS trb,

SUM( CASE WHEN dovizID = 2 THEN CASE WHEN meblag > 0 THEN @USD_bakiye:= @USD_bakiye + @EUR_bakiye + @TRY_bakiye + dovizKarsiligi ELSE @USD_bakiye:= @USD_bakiye:= -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS usb,

SUM( CASE WHEN dovizID = 3 THEN CASE WHEN meblag > 0 THEN @EUR_bakiye:= @EUR_bakiye + dovizKarsiligi ELSE @EUR_bakiye:=@EUR_bakiye -1*dovizKarsiligi END ELSE 0 END ) OVER (ORDER BY islemTarihi asc ) AS eub

FROM
    al_islemler 
WHERE
    al_islemler.cariID = 33 
    AND al_islemler.firmaID = 382 
GROUP BY
    id 
ORDER BY
    al_islemler.islemTarihi ASC,
    al_islemler.id DESC 
    LIMIT 0,
    10;

sample image my software

Solution

MySQL 8 introduced window functions, which come here very handy

SELECT * 
  , 
  SUM(CASE WHEN dovizID =1 THEN  `amount` * `exchange rate` ELSE 0  END) OVER (ORDER BY `id`) as  `TRY_balance`,
   SUM(CASE WHEN dovizID =2 THEN  `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
   SUM(CASE WHEN dovizID =3 THEN  `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS  `EUR_balance` 
  
  FROM balance

id	amount	foreignExchangeEquivalentTl	exchange rate	dovizID	TRY_balance	USD_balance	EUR_balance
452	-60	1938.63	32.31	2	0.00	-1938.60	0.00
455	100	3514.00	35.14	3	0.00	-1938.60	3514.00
456	-300	300.00	1.00	1	-300.00	-1938.60	3514.00

fiddle

CREATE TABLE balance (
  `id` INTEGER,
  `amount` INTEGER,
  `foreignExchangeEquivalentTl` DECIMAL(10,2),
  `exchange rate` DECIMAL(10,2),
  `dovizID` INTEGER
);

INSERT INTO balance
  (`id`, `amount`, `foreignExchangeEquivalentTl`, `exchange rate`, `dovizID` )
VALUES
  ('452', '-60', '1938.63', '32.31', '2'),
  ('455', '100', '3514.00', '35.14', '3'),
  ('456', '-300', '300', '1', '1');

Records: 3  Duplicates: 0  Warnings: 0

SELECT 
  `id`, `amount`
  , `exchange rate`, `dovizID`,
  SUM(CASE WHEN dovizID =1 THEN  `amount` * `exchange rate` ELSE 0  END) OVER (ORDER BY `id`) as  `TRY_balance`,
   SUM(CASE WHEN dovizID =2 THEN  `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
   SUM(CASE WHEN dovizID =3 THEN  `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS  `EUR_balance` 
  
  FROM balance

id	amount	exchange rate	dovizID	TRY_balance	USD_balance	EUR_balance
452	-60	32.31	2	0.00	-1938.60	0.00
455	100	35.14	3	0.00	-1938.60	3514.00
456	-300	1.00	1	-300.00	-1938.60	3514.00

fiddle

SELECT 
  `id`, `amount`
  , `exchange rate`, `dovizID`,
  SUM(CASE WHEN dovizID =1 THEN  `amount` * `exchange rate` ELSE 0  END) OVER (ORDER BY `id`) as  `TRY_balance`,
   SUM(CASE WHEN dovizID =2 THEN  `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) AS `USD_balance`,
   SUM(CASE WHEN dovizID =3 THEN  `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) AS  `EUR_balance` 
  ,SUM(`amount` * `exchange rate`) OVER (ORDER BY `id`) as total_balance
  FROM balance

id	amount	exchange rate	dovizID	TRY_balance	USD_balance	EUR_balance	total_balance
452	-60	32.31	2	0.00	-1938.60	0.00	-1938.60
455	100	35.14	3	0.00	-1938.60	3514.00	1575.40
456	-300	1.00	1	-300.00	-1938.60	3514.00	1275.40

fiddle

the best that i can do

SELECT 
  `id`, `amount`
  , `exchange rate`, `dovizID`,
  CASE WHEN dovizID =1 THEN
  SUM(CASE WHEN dovizID =1 THEN  `amount` * `exchange rate` ELSE 0  END) OVER (ORDER BY `id`) ELSE 0 ENd  as  `TRY_balance`,
   CASE WHEN dovizID =2 THEN SUM(CASE WHEN dovizID =2 THEN  `amount` * `exchange rate` ELSE 0 END ) OVER (ORDER BY `id`) ELSE 0 ENd AS `USD_balance`,
   CASE WHEN dovizID =3 THEN SUM(CASE WHEN dovizID =3 THEN  `amount` * `exchange rate` ELSE 0 END) OVER (ORDER BY `id`) ELSE 0 END  AS  `EUR_balance` 
  ,SUM(`amount` * `exchange rate`) OVER (ORDER BY `id`) as total_balance
  FROM balance

id	amount	exchange rate	dovizID	TRY_balance	USD_balance	EUR_balance	total_balance
452	-60	32.31	2	0.00	-1938.60	0.00	-1938.60
455	100	35.14	3	0.00	0.00	3514.00	1575.40
456	-300	1.00	1	-300.00	0.00	0.00	1275.40

fiddle