CODEWITHSUNDEEP
mysqlsqlgroup-byhaving
Cratylus
Cratylus

Reputation: 54074

Group by with having on a non-aggregate function does not work

Why is this SQL statement:
select team,avg(salary) 'Average Salary' from orgchart group by team having salary <38000;

Gives the following error?

mysql> select team,avg(salary) 'Average Salary' from orgchart group by team having salary <38000; ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

With a group by I can only use having and not where.Correct.
The following works:

select team,avg(salary) 'Average Salary' from orgchart group by team having avg(salary)<38000;

Upvotes: 1

Views: 156

Answers (2)

Andriy M
Andriy M

Reputation: 77657

HAVING is used to evaluate a group of rows. Prior to grouping, you can still evaluate/filter out rows individually, by applying a WHERE condition to them.

So, in your case it should likely be

select team, avg(salary) 'Average Salary'
from orgchart
where salary < 38000
group by team;

assuming that you want to calculate the average salary among the people with the salary below 38000 (separately for every team).

However, if you are actually trying to filter out teams where the average salary is less than 38000, then having is the right choice, but you should compare avg(salary), not salary, with the given value, i.e. like this:

select team, avg(salary) 'Average Salary'
from orgchart
group by team
having avg(salary) < 38000;

Upvotes: 7

JodyT
JodyT

Reputation: 4412

A where clause applies to individual rows while having applies to groups. More on this can be found at: difference-between-having-and-where-clause

Upvotes: 2

Related Questions