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:

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

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: CustomerName OrderPrice Baldwin 2500 Smith 1400

CustomerName	OrderPrice
Baldwin	2500
Smith	1400

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.