3

I have a query that sums up two values in the same table and then calculates one as a percentage of the other

select account_no,
  100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100)))
from accounts etc

What I want to do is only return results where that value is greater than a certain amount (say 4 in this case). If I add something in the where clause such as:

where 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

I get the error:

An aggregate may not appear in the WHERE clause...

I expect there's an easy way round this that I haven't learned yet. Any ideas?

0
6

Try:

HAVING 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

However you will still probably find that integer math is preventing you from getting the result you want. So this might be better:

HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4
0
2

You forget group by account_no. So finaly:

select account_no,
  100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100)))
from accounts etc
group by account_no
HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4
1
  • 3
    I assume based on etc that the group by is there and the query works fine without the filter. The problem was that they were using WHERE (which has to filter before aggregation) instead of HAVING (which can filter after). Jul 28 '15 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.