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.
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;
- "SQL HAVING Clause". w3schools.com. "The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions."