Choosing Rows with the HAVING Clause
The HAVING clause sets conditions on the GROUP BY clause similar to the way WHERE interacts with SELECT. The WHERE search condition is applied before the grouping operation occurs; the HAVING search condition is applied after the grouping operation occurs. The HAVING syntax is similar to the WHERE syntax, except HAVING can contain aggregate functions. HAVING clauses can reference any of the items that appear in the select list.
The following example groups the SalesOrderDetail table by product ID and includes only those groups of products that have orders totaling more than $1,000,000 and whose average order quantities are less than 3.
USE AdventureWorks; GO SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $1000000.00 AND AVG(OrderQty) < 3 ; GO
Note that when multiple conditions are included in HAVING, they are combined with AND, OR, or NOT.
To see the products that have had total sales greater than $2,000,000, use the following query:
USE AdventureWorks; GO SELECT ProductID, Total = SUM(LineTotal) FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING SUM(LineTotal) > $2000000.00 ; GO
Here is the result set.
ProductID Total ----------- ---------------------- 781 3864606.54937208 969 2010943.97244001 793 2897478.01200001 784 3699803.72383008 780 3880441.60780208 976 2079038.42948 795 2268057.09000002 783 4548164.01783709 779 4170215.3849281 782 5032968.13026809 794 2679200.01336002 753 2006264.4236 (12 row(s) affected)
If you want to make sure there are at least one thousand five hundred items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold. The query looks like this:
USE AdventureWorks; GO SELECT ProductID, SUM(LineTotal) AS Total FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING COUNT(*) > 1500 ; GO
Understanding the correct sequence in which the WHERE, GROUP BY, and HAVING clauses are applied helps in coding efficient queries:
The WHERE clause is used to filter the rows that result from the operations specified in the FROM clause.
The GROUP BY clause is used to group the output of the WHERE clause.
The HAVING clause is used to filter rows from the grouped result.
For any search conditions that could be applied either before or after the grouping operation, it is more efficient to specify them in the WHERE clause. This reduces the number of rows that have to be grouped. The only search conditions that should be specified in the HAVING clause are those search conditions that must be applied after the grouping operation has been performed.
The Microsoft SQL Server query optimizer can deal with most of these conditions. If the query optimizer determines that a HAVING search condition can be applied before the grouping operation, it will do so. The query optimizer might not be able to recognize all of the HAVING search conditions that can be applied before the grouping operation. It is recommended that you place all such search conditions in the WHERE clause instead of the HAVING clause.
This example shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by Product ID and eliminates products whose average order quantities are five or less.
USE AdventureWorks; GO SELECT ProductID FROM Sales.SalesOrderDetail GROUP BY ProductID HAVING AVG(OrderQty) > 5 ORDER BY ProductID ; GO
The following example shows a HAVING clause without aggregate functions. It groups the rows in the ProductModel table by name and eliminates those names that do not start with Mountain.
USE AdventureWorks; GO SELECT pm.Name, AVG(ListPrice) AS 'Average List Price' FROM Production.Product AS p JOIN Production.ProductModel AS pm ON p.ProductModelID = pm.ProductModelID GROUP BY pm.Name HAVING pm.Name LIKE 'Mountain%' ORDER BY pm.Name ; GO
Note that the ORDER BY clause can be used to order the output of a GROUP BY clause.