Balance statement is not showing correctly

Question

I have a 10.1.31-MariaDB database like below table. I need to make balance sheet accroding to every transaction. But balance is not showing correctly.

---------------------------------------
| id | item | iIN | iOut |    date    |
---------------------------------------
|5   | A    |     | 3    | 2023-01-05 |
---------------------------------------
|4   | B    | 5   |      | 2023-01-04 |
---------------------------------------
|3   | A    |     | 2    | 2023-01-03 |
---------------------------------------
|2   | B    | 5   |      | 2023-01-02 |
---------------------------------------
|1   | A    | 10  |      | 2023-01-01 |
---------------------------------------

I have tried the below query but the result is not showing as expected. I want to get results as below table. Please help me for the correct query. Thanks in advance .

SELECT  t.`id`, t.`item`, t.`iIN`, t.`iOut`, t.`date`, IFNULL(qin.Quantity, 0) - IFNULL(qout.Quantity, 0) AS Quantity

FROM `transaction` t
 
LEFT JOIN (

    SELECT `id`, `item`, SUM(`iIN`) AS Quantity

    FROM transaction

    GROUP BY `id`

) qin ON t.`id` = qin.`id`
 
LEFT JOIN (

    SELECT `id`,`item`, SUM(`iOut`) AS Quantity

    FROM transaction

    GROUP BY `id`
    ORDER BY date DSC

) qout ON t.`id` = qout.`id`

Expected result

-----------------------------------------------
| id | item | iIN | iOut |    date    |Balance|
-----------------------------------------------
|5   | A    |     | 3    | 2023-01-05 | 5     |
-----------------------------------------------
|4   | B    | 5   |      | 2023-01-04 | 10    |
-----------------------------------------------
|3   | A    |     | 2    | 2023-01-03 | 8     |
-----------------------------------------------
|2   | B    | 5   |      | 2023-01-02 | 5     |
-----------------------------------------------
|1   | A    | 10  |      | 2023-01-01 | 10    |
-----------------------------------------------

Answer 1

SELECT *,
       (SELECT COALESCE(SUM(iIN), 0) - COALESCE(SUM(iOUT), 0)
        FROM transaction t2
        WHERE t1.item = t2.item
          AND t1.`date` >= t2.`date`) balance
FROM transaction t1