Having (SQL)

AHAVINGclause inSQLspecifies that an SQLSELECTstatement must only return rows where aggregate values meet the specifiedconditions.

Use

HAVINGandWHEREare often confused by beginners, but they serve different purposes.WHEREis taken into account at an earlier stage of a query execution, filtering the rows read from the tables. If a query containsGROUP BY,rows from the tables are grouped and aggregated. After the aggregating operation,HAVINGis applied, filtering out the rows that don't match the specified conditions. Therefore,WHEREapplies to data read from tables, andHAVINGshould only apply to aggregated data, which isn't known in the initial stage of a query.

To view the present condition formed by theGROUP BYclause, theHAVINGclause is used.

Examples

To return a list of department IDs whose total sales exceeded $1000 on the date of January 1, 2000, along with the sum of their sales on that date:

SELECTDeptID,SUM(SaleAmount)
FROMSales
WHERESaleDate='2000-01-01'
GROUPBYDeptID
HAVINGSUM(SaleAmount)>1000

Referring to thesample tables in theJoinexample,the following query will return the list of departments which have more than 1 employee:

SELECTDepartmentName,COUNT(*)
FROMEmployee
JOINDepartmentONEmployee.DepartmentID=Department.DepartmentID
GROUPBYDepartmentName
HAVINGCOUNT(*)>1;

HAVINGis convenient, but not necessary. Code equivalent to the example above, but without usingHAVING,might look like:

SELECT*FROM(
SELECTDepartmentNameASdeptNam,COUNT(*)ASempCount
FROMEmployeeASemp
JOINDepartmentASdeptONemp.DepartmentID=dept.DepartmentID
GROUPBYdeptNam
)ASgrp
WHEREgrp.empCount>1;

External links

• The HAVING and GROUP BY SQL clausesArchived2011-06-03 at theWayback Machine
• SQL Aggregate FunctionsArchivedMay 3, 2017, at theWayback Machine