Having (SQL)

A HAVING statement in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions.

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:

 SELECT DeptID, SUM(SaleAmount)
 FROM Sales
 WHERE SaleDate = '01-Jan-2000'
 GROUP BY DeptID
 HAVING SUM(SaleAmount) > 1000

Referring to the sample tables in the Join (SQL) example, the following query will return the list of departments who have more than 1 employee:

 SELECT DepartmentName, COUNT(*) 
 FROM employee,department 
 WHERE employee.DepartmentID = department.DepartmentID 
 GROUP BY DepartmentName
 HAVING COUNT(*)>1;

External links

• The HAVING and GROUP BY SQL clauses
• SQL Aggregate Functions