Having (SQL)
A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added[when?] to the SQL language because the WHERE keyword could not be used with aggregate functions.[1]
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 example, the following query will return the list of departments which have more than 1 employee:
SELECT DepartmentName, COUNT(*)
FROM Employee, Department
WHERE Employee.DepartmentID = Department.DepartmentID
GROUP BY DepartmentName
HAVING COUNT(*)>1;
HAVING is convenient, but not necessary. Code equivalent to the example above, but without using HAVING, might look like:
SELECT * FROM (
SELECT DepartmentName AS deptNam, COUNT(*) AS empCnt
FROM Employee AS emp, Department AS dept
WHERE emp.DepartmentID = dept.DepartmentID
GROUP BY deptNam
) AS grp
WHERE grp.empCnt > 1;
References
- ^ "SQL HAVING Clause". w3schools.com.
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.