I want to calculate account balance using raw sql without extra application logic. Transaction schema includes amount, from_account_id and to_account_id
My query is
SELECT SUM(tdebit.amount) - SUM(tcredit.amount) as balance
FROM accounts as a
INNER JOIN transactions as tdebit ON a.id = tdebit.to_account_id
INNER JOIN transactions as tcredit ON a.id = tcredit.from_account_id
WHERE a.id = $1 AND tdebit.succeed = true AND tcredit.succeed = true
And it does not work as I expected - result is wrong, but if I join transaction only once it works correctly, for example just debit amount is ok
SELECT SUM(tdebit.amount) as debit
FROM accounts as a
INNER JOIN transactions as tdebit ON a.id = tdebit.to_account_id
WHERE a.id = $1 AND tdebit.succeed = true
What did I miss in my balance query?
You are basically calculating the cross product between a
tdebits
andtcredits
, i.e. for each row intdebits
you are iterating over all the rows intcredits
. There's also no reason to join toaccounts
(unlessto_account_id
andfrom_account_id
aren't foreign keys).You only need to do one pass over transactions and you just need to know whether the amount is a credit or debit.
If an account can transfer to itself, add a
t.to_account_id <> t.from_account_id
.Aggregate before doing the joins, if you want the value for all accounts: