The Having Clause

You have already learnt how to filter individual values based on a given condition. But how do you do this for grouped values? Suppose your manager asks you to count all the employees whose salaries are more than the average salary in their respective departments. Now, intuitively, you know that two aggregate functions would be used here: count() and avg(). You decide to apply the ‘where’ condition on the average salary of the department, but to your surprise, the query fails. This is exactly what the ‘having‘clause is for. So, in the upcoming video, you will learn how the ‘having’ clause is used in a query.

To summarise, you are now aware of all the SQL clauses: ‘select’, ‘from’, ‘where’, ‘group by’, ‘order by’ and ‘having’.

The ‘having’clause is typically used when you have to apply a filter condition on an ‘aggregated value’.This is because the ‘where’ clause is applied before aggregation takes place and, thus, it is not useful when you want to apply a filter on an aggregated value.

In other words, the ‘having’clause is equivalent to a ‘where’ clause after the ‘group by’ clause has been executed but before the ‘select’ clause is executed. You can read this StackOverflow answer to understand the ‘having’ clause better.

It is important to not get confused between the ‘having’ and ‘where’ clauses. For example, if you want to display the list of all the employees whose salary >= 30,000, then you can use the ‘where’ clause, since there is no aggregation taking place in this query. But if you want to display the list of all the employees whose salary <= the average salary, where avg() is the aggregation function, then you will have to use the ‘having’ clause.

In the next segment, you will learn the strings and the date-time functions.

Report an error