MacLochlainns Weblog

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.