ERROR 1111 (HY000): Invalid use of group function

Question

Given a staff table with the following attributes:

ec,name,code,dob,salary

List the staff members earning more than the average salary.

My solution:

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

What is wrong with it?

Answer 1

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Example using WHERE clause :

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

Example using HAVING clause :

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

Having clause specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.