# Only show results greater than a certain number when summing two values together

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?

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
``````

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
``````
• 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