The SQL HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. The HAVING clause behaves like the WHERE clause, but is applicable to groups - the rows in the result set representing groups. In contrast the WHERE clause is applied to individual rows, not to groups.
To clarify how exactly HAVING works, we'll use the Sales table:
In the previous chapter we retrieved a list with all customers along with the total amount each customer has spent respectively and we use the following statement:
This time we want to select all unique customers, who have spent more than 1200 in our store. To accomplish that we'll modify the SQL statement above adding the HAVING clause at the end of it:
The result of the SELECT query after we added the HAVING search condition is below:
Another useful example of the HAVING clause, will be if we want to select all customers that have ordered more than 5 items in total from all their orders. OUR HAVING statement will look like this:
You can have both WHERE and HAVING in one SELECT statement. For example you want to select all customers who have spent more than 1000, after 10/01/2005. The SQL statement including both HAVING and WHERE clauses will look like this:
Here is something very important to keep in mind. The WHERE clause search condition is applied to each individual row in the Sales table. After that the HAVING clause is applied on the rows in the final result, which are a product of the grouping. The important thing to remember is that the grouping is done only on the rows that satisfied the WHERE clause condition.