SQL Having

We have seen previously that we can filter data using the WHERE clause and aggregate data using the GROUP BY clause. 

While the former can be combined in a SELECT statement with the latter, in order to filter data before it is aggregated, one might initially think that it could also be used to do so afterward. 

For example,  if we wanted to find an employee last name that occurs in the employees table exactly 150 times,  we would assume that the query below would be valid:

SELECT last_name, COUNT(*) FROM employees GROUP BY last_name WHERE COUNT(*)=150;

However, here the WHERE clause cannot be applied on the result of COUNT, as it filters data before the aggregation occurs and so can only be applied with predicates using  columns in the source table.

The SQL HAVING clause serves to be used in these instances instead, and functions as a WHERE clause for aggregate calculations, which are evaluated after the initial application of WHERE during a query’s execution.

As such, HAVING follows the GROUP BY clause in an aggregate query. In the simplest case, it would appear in the general form as below:

SELECT <expression> FROM <table> GROUP BY <columns> HAVING <predicate>;

Where the predicate here uses expressions including aggregate functions.

In more complex queries, the clause should come after WHERE (as it is before GROUP BY) and precede the usage of LIMIT and ORDER BY if they are included:

SELECT <expression> FROM <table> WHERE <predicate> 
GROUP BY <columns> HAVING <predicate>
ORDER BY <column> LIMIT <row count>;

Let’s return to our example where we are looking for the last name shared by exactly 200 employees in the employees table. In this case, using HAVING is straightforward, as it is simply used instead of WHERE:

SELECT last_name, COUNT(*) FROM employees HAVING COUNT(*)=150;