HAVING Clause
HAVING Clause
Learning Outcomes
- Learn how to filter an aggregated set.
Lesson Materials
As explained in the SELECT statement page, the minimum SELECT statement does not require a HAVING clause. The HAVING clause is only required when a query uses an aggregated function. You may use a HAVING clause with any query that uses an aggregated function in the SELECT-list whether or not it uses a GROUP BY clause.
SELECT-list
The SELECT-list may include non-aggregated and aggregated columns. The non-aggregated columns may be literals, calculations of literals and columns. Aggregated columns use functions like these:
- AVG
- COUNT
- MAX
- MIN
- SUM
- STD
Queries that use non-aggregated and aggregated functions together require you to use a GROUP BY clause; whereas, queries that use only aggregated functions don’t require a GROUP BY clause.
SELECT-list with only aggregated functions
The following query builds on the example in the GROUP BY clause by adding a HAVING clause. The HAVING clause filters only the aggregated row set. It returns only those rows that return an average cost per ounce greater than twenty-two cents.
1 2 3 4 5 6 7 | COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY brand HAVING AVG(suggested_retail/ounces) > .22; |
It returns the following nine rows from the thirty-six aggregated rows:
Average Cost Brand Ounces ------------------------------ ---------- Blueberry Toast Crunch $0.27 Apple Toast Crunch $0.27 Chocolate Cheerios $0.23 Strawberry Toast Crunch $0.27 Lucky Charm $0.24 Cocoa Puffs $0.31 Cinnamon Vanilla Lucky Charm $0.24 Cocoa Pebbles $0.31 Trix $0.24 |
The HAVING clause is the last clause that effects the number of rows returned. After the HAVING clause, the only clause that follows it is the ORDER BY clause. The ORDER BY clause simply orders the rows already returned.
