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.

See Also

Reference