SQL Question -- why can't I use an aggregate function here?

[Janeice DelVecchio]

This, to me, looks like it should return the department_id and AVG(salary) where the AVG(salary) is greater than $8000..... but the tutorial says it returns an error....

Why?

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;    

from Skillsoft SQL course - creating and manipulating group functions

Okay, so if this is synctactically incorrect.... how SHOULD it look? Do I need a subquery instead?

[Jeanne Boyarsky]

Janeice,
Aggregate functions can't go in the where clause because that goes against each row rather than the grouped function. Instead it goes in the "having" clause.

[Janeice DelVecchio]

Thanks, Jeanne! The book had something complicated to say about the order the statement gets processed. Your answer makes a ton more sense.

So I would do this:

SELECT department_id, AVG(salary)   
FROM employees   
GROUP BY department_id
HAVING AVG(salary) > 8000;       

Or this (I think) would be ok too:

SELECT department_id, AVG(salary)   
FROM employees   
WHERE department_id BETWEEN 10 AND 30
GROUP BY department_id
HAVING AVG(salary) > 8000;       

Great!

Can I use an ORDER BY with the HAVING, too?

[Christophe Verré]

Janeice DelVecchio wrote:
Can I use an ORDER BY with the HAVING, too?

Yes.