Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.
Transact-SQL Syntax Conventions
[ HAVING <search condition> ]
<search_condition> Specifies one or more predicates for groups and/or aggregates to meet. For more information about search conditions and predicates, see Search Condition (Transact-SQL).
The text, image, and ntext data types cannot be used in a HAVING clause.
The following example that uses a simple HAVING
clause retrieves the total for each SalesOrderID
from the SalesOrderDetail
table that exceeds $100000.00
.
USE AdventureWorks2012 ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;
The following example uses a HAVING
clause to retrieve the total for each SalesAmount
from the FactInternetSales
table when the OrderDateKey
is in the year 2004 or later.
-- Uses AdventureWorks
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING SUM(SalesAmount) > 80000
ORDER BY OrderDateKey;