HAVING (Transact-SQL)

HAVING (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

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.

Topic link icon Transact-SQL Syntax Conventions


[ HAVING <search condition> ]
-- Azure SQL Data Warehouse and 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;

Community Additions

ADD
Show:
© 2016 Microsoft