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.[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.
External links
This article is issued from Wikipedia - version of the 3/28/2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.