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 to the SQL language because the WHERE keyword could not be used with aggregate functions.

The HAVING clause filters the data on the group row but not on the individual row.

To view the present condition formed by the GROUP BY clause, the HAVING clause is used.


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'
 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 is convenient, but not necessary. Code equivalent to the example above, but without using HAVING, might look like:

   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;
This article is issued from Wikipedia. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.