| title | HAVING (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | SELECT - HAVING (Transact-SQL) | ||||
| author | VanMSFT | ||||
| ms.author | vanto | ||||
| ms.date | 01/21/2020 | ||||
| ms.service | sql | ||||
| ms.subservice | t-sql | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
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.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 AdventureWorks2022;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ; Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]
The following example uses a HAVING clause to retrieve the total SalesAmount that exceeds 80000 for each OrderDateKey from the FactInternetSales table.
-- Uses AdventureWorks
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING SUM(SalesAmount) > 80000
ORDER BY OrderDateKey;