ERROR 1111 (HY000): misuse of group function

Question

given the following table staff (ec, name, code, dob, salary)

Q. List of employees earning more than average salary

My sun. select* from staff where salary > avg(salary);

What's bad about it?

Answer 1

An aggregate may not appear in the WHERE if it is not in the subquery contained in the HAVING or selection list, and the aggregated column is an external link.

Example using the WHERE :

 select * from staff where salary > (select avg(salary) from staff) 

Example using the HAVING :

 select deptid,COUNT(*) as TotalCount from staff group by deptid having count(*) > 2 

HAVING sets the search term for a group or aggregate. HAVING can only be used with a SELECT . HAVING commonly used in a GROUP BY . If GROUP BY not used, HAVING behaves like a WHERE .