SQL HAVING

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:

OrderIDOrderDateOrderPriceOrderQuantityCustomerName
112/22/20051602Smith
208/10/20051902Johnson
307/13/20055005Baldwin
407/15/20054202Smith
512/22/200510004Wood
610/2/20058204Smith
711/03/200520002Baldwin

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:

SELECT CustomerName, SUM(OrderPrice) FROM Sales GROUP BY CustomerName

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:

SELECT CustomerName, SUM(OrderPrice) FROM Sales GROUP BY CustomerName HAVING SUM(OrderPrice) > 1200

The result of the SELECT query after we added the HAVING search condition is below:

CustomerNameOrderPrice
Baldwin2500
Smith1400

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:

SELECT CustomerName, SUM(OrderQuantity) FROM Sales GROUP BY CustomerName HAVING SUM(OrderQuantity) > 5

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:

SELECT CustomerName, SUM(OrderPrice) FROM Sales WHERE OrderDate > '10/01/2005' GROUP BY CustomerName HAVING SUM(OrderPrice) > 1000

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.