SELECT - HAVING (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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
Syntax
[ HAVING <search condition> ]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
<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.
Examples
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: Azure Synapse Analytics and Analytics Platform System (PDW)
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;