HAVING clause

<< Click to Display Table of Contents >>

Navigation:  Local SQL > Data Manipulation Language > Clauses >

HAVING clause

Previous pageReturn to chapter overviewNext page
hmtoggle_plus1See also

Specifies filtering conditions for a SELECT statement.

HAVING predicates

Description

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)