so, I have been trying to create a ledger-like database using MySQL.

which primarily, stores the transaction history of all Users done throughout the app, regardless of the type(Credit/Debit) and User.

This ledger-like database is also responsible for dynamically calculating the balance, after each deposit (credits)[it will also calculate the balance dynamically for each debit, but for simplicity let's just focus on one type).

The link to the SQL fiddle:

so, it was going nice for the first 4 records, with acc_id VA473, and the balance was also calculated properly. But the 5th and 6th entry, was by a different acc_id, but the balance is being calculated from the 4th entry.

the correct balance for 5th row will be: (balance credit - debit) => (0 (100 -0)) = 100. [assuming the balance will be 0 if not set otherwise)

and 6th row will be: (balance credit - debit) => (100 (150-0)) = 250

Thanks in advance for helping :)

CodePudding user response：

CREATE TABLE test (
  id INT,
  utr VARCHAR(255),
  acc_id VARCHAR(255),
  credit INT,
  debit INT
  );
INSERT INTO test (id, utr, acc_id, credit, debit) VALUES (1, 'xxyyzz1', 'VA743', 30, 0);
INSERT INTO test (id, utr, acc_id, credit, debit) VALUES (2, 'xxyyzz2', 'VA743', 70, 0);
INSERT INTO test (id, utr, acc_id, credit, debit) VALUES (3, 'xxyyzz3', 'VA743', 80, 0);
INSERT INTO test (id, utr, acc_id, credit, debit) VALUES (4, 'xxyyzz4', 'VA700', 80, 0);
INSERT INTO test (id, utr, acc_id, credit, debit) VALUES (5, 'xxyyzz5', 'VA700', 70, 0);
SELECT * FROM test;

SELECT @balance := (acc_id = @acc_id) * @balance   credit - debit balance,
       test.id,
       test.utr,
       @acc_id := test.acc_id acc_id,
       test.credit,
       test.debit
FROM test
CROSS JOIN (SELECT @balance:=0, @acc_id:='') init_variables
ORDER BY test.acc_id, test.id;

fiddle

Pay attention - the ORDER BY clause is added, and its presence is critical. W/o it the query is not deterministic and may produce incorrect output.