CODEWITHSUNDEEP
mysql
user2809974
user2809974

Reputation: 31

Mysql database query not working as expected

Why can't I write queries like 

select * 
from STAFF 
having Salary > avg(salary);

in SQL?

PS: to find staff members with a salary = avg salary, I need to write 

select * 
from STAFF 
having Salary > (select avg(Salary) from STAFF);

Is there any other way to do this?

Upvotes: 2

Views: 45

Answers (1)

Raging Bull
Raging Bull

Reputation: 18737

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.

The functions SUM(),AVG(),MIN(),MAX(),COUNT(),etc are called aggregate functions. Read more here.

Example using WHERE clause :

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

See example in SQL Fiddle.

Example using HAVING clause :

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

See example in SQL Fiddle.

Where can we use having clause:

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.

Read more here.

Upvotes: 2

Related Questions