Jump to content

Having (SQL)

From Wikipedia, the free encyclopedia
This is an old revision of this page, as edited by Victor Yus (talk | contribs) at 13:30, 21 August 2012 (Victor Yus moved page Having (SQL) to HAVING: caps distinguishes it). The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

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 (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;

HAVING is convenient, but not necessary. Here is the above example without using HAVING:

select * from (
   SELECT DepartmentName DeptNm, COUNT(*) EmpCnt 
   FROM employee as emp, department as dept 
   WHERE emp.DepartmentID = dept.DepartmentID GROUP BY DeptNm) as grp 
where grp.EmpCnt > 1;

References

  1. ^ "SQL HAVING Clause". w3schools.com. The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.