<< Click to Display Table of Contents >> HAVING clause |
Specifies filtering conditions for a SELECT statement.
HAVING predicates
Use a HAVING clause to limit the rows retrieved by a SELECT statement to a subset of rows where aggregated column values meet the specified criteria. A HAVING clause can only be used in a SELECT statement when:
•The statement also has a GROUP BY clause.
•One or more columns are the subjects of aggregate functions.
The value for a HAVING clause is one or more logical expressions, or predicates, that evaluate to true or false for each aggregate row retrieved from the table. Only those rows where the predicates evaluate to true are retrieved by a SELECT statement. For example, the SELECT statement below retrieves all rows where the total sales for individual companies exceed $1,000.
SELECT Company, SUM(sales) AS TOTALSALES
FROM Sales1998
GROUP BY Company
HAVING (SUM(sales) >= 1000)
ORDER BY Company
Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria.
A SELECT statement can include both a WHERE clause and a HAVING clause. The WHERE clause filters the data to be aggregated, using columns not the subject of aggregate functions. The HAVING clause then further filters the data after the aggregation, using columns that are the subject of aggregate functions. The SELECT query below performs the same operation as that above, but data limited to those rows where the State column is "CA".
SELECT Company, SUM(sales) AS TOTALSALES
FROM Sales1998
WHERE (State = "CA")
GROUP BY Company
HAVING (SUM(Sales) >= 1000)
ORDER BY Company
Subqueries are supported in the HAVING clause. A subquery works like a search condition to restrict the number of rows returned by the outer, or "parent" query. The following statement uses a subquery to total all the sales for California and calculates a value half that total. This calculated value is then used in the parent statement to restrict the result set to aggregated rows where the grouped subtotal (based on customer number) is at least as much as the calculated value.
SELECT O.CustNo, SUM(O.ItemsTotal)
FROM Orders O
GROUP BY O.CustNo
HAVING SUM(O.ItemsTotal) >=
(SELECT SUM(O2.ItemsTotal) / 2
FROM Customer C,Orders O2
WHERE (C.CustNo = O2.CustNo) AND
(C.State = "CA"))
Note: A HAVING clause filters data after the aggregation of a GROUP BY clause. For filtering based on row values prior to aggregation, use a WHERE clause.
Applicability
SELECT statements (with GROUP BY clauses and data aggregation)