Skip to content

select-having-transact-sql.md

Latest commit

 

History

History
83 lines (68 loc) · 3 KB

select-having-transact-sql.md

File metadata and controls

83 lines (68 loc) · 3 KB

title: "HAVING (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "11/28/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database, sql-data-warehouse, pdw" ms.service: "" ms.component: "t-sql|queries" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
  • "HAVING"
  • "HAVING_TSQL" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "restricting conditions for result set"
  • "search conditions [SQL Server], HAVING clause"
  • "HAVING clause"
  • "HAVING clause, about HAVING clause" ms.assetid: 55650709-001e-42f4-902f-ead09a3c34af caps.latest.revision: 27 author: "douglaslMS" ms.author: "douglasl" manager: "craigg" ms.workload: "Active" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

SELECT - HAVING (Transact-SQL)

[!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 with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

Topic link icon Transact-SQL Syntax Conventions

Syntax

[ HAVING <search condition> ]

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 AdventureWorks2012 ;  
GO  
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
FROM Sales.SalesOrderDetail  
GROUP BY SalesOrderID  
HAVING SUM(LineTotal) > 100000.00  
ORDER BY SalesOrderID ;

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

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;

See Also

GROUP BY (Transact-SQL)
WHERE (Transact-SQL)