I have two tables:
(i) a balance table that shows the balance for each of the users
(ii) a transaction table that has the debit and credit transactions for each of the users
I need to check if any user has a discrepancy between his/her balance in the balance table and the sum of his/her transactions in the transaction table.
When I enter the below query:
select sum(transaction.credit)-sum(transaction.debit),balance.balance
from transaction
right join balance on balance.user_id = transaction.user_id
where balance.balance <> sum(transaction.credit)-sum(transaction.debit)
group by transaction.user_id
I get an error with a message "Invalid use of group function". But even if I remove the last row, I still get the same message.
What am I doing wrong?