# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## 5 – 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.

Written by maclochlainn

December 24th, 2017 at 11:34 pm