| title | HAVING (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 08/09/2017 | ||||
| ms.prod | sql-non-specified | ||||
| ms.reviewer | |||||
| ms.suite | |||||
| ms.technology |
|
||||
| ms.tgt_pltfrm | |||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 55650709-001e-42f4-902f-ead09a3c34af | ||||
| caps.latest.revision | 27 | ||||
| author | BYHAM | ||||
| ms.author | rickbyh | ||||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-all_md]
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
Transact-SQL Syntax Conventions
-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
[ HAVING <search condition> ]
<search_condition> Specifies the search condition for the group or the aggregate to meet.
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;