1

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?

Shadow
  • 33,525
  • 10
  • 51
  • 64
TurboS
  • 13
  • 2
  • Second point is that this query is absolutely useless, it doesn't help you identify descrepancies. It's output will be a to numbers but there is no way for you to identify what rows those numbers actuallly came from – e4c5 Oct 03 '16 at 12:51
  • @e4c5 the error itself is raised because the OP uses aggregate functions in the where clause. – Shadow Oct 03 '16 at 13:14
  • oops @shadow, I meant to type projection and not projects :) – e4c5 Oct 03 '16 at 13:17

2 Answers2

1

USE Having Instead of Where clause

select transaction.user_id
from transaction
right join balance on balance.user_id = transaction.user_id
group by transaction.user_id
Having sum(transaction.credit)-sum(transaction.debit) <> balance.balance
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
  • thanks JaydipJ. however, I got an error "Unknown column 'balance.balance' in 'having clause'". – TurboS Oct 04 '16 at 18:57
0

Conditions on aggregate functions should not be put in the where clause, but in the having clause:

select      balance.user_id, 
            coalesce(sum(transaction.credit), 0) 
             - coalesce(sum(transaction.debit), 0) trans_balance, 
            balance.balance
from        transaction
right join  balance 
         on balance.user_id = transaction.user_id
group by    balance.user_id
having      balance.balance <> trans_balance

Note that it is better to group by balance.user_id as there might not be a matching transaction record. You should in that case also use coalesce as otherwise the comparison will fail on null.

trincot
  • 317,000
  • 35
  • 244
  • 286