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.