SELECT gender, AVG(age) FROM employee WHERE AVG(age)>30 GROUP BY gender
When this command is executed, it gives the following error:
Msg 147, Level 16, State 1, Line 1
Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list; the column being aggregated is an outer reference.
Msg 147, Level 16, State 1, Line 1
Invalid column name ‘gender’.
This means that whenever we work with aggregate functions and use the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.
When we use the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause.
select e_gender, avg(e_age) from employee group by e_gender having avg(e_age)>30
Output: