# CHAPTER 12: HAVING Clause

A `HAVING` clause is used to specify a condition which a group must satisfy in order to be selected. It applies to the most deeply nested group in the `SELECT` command, and can only be used when every selected item is either grouped, an aggregate function (`AVG`, `MAX`, etc.) or a constant.

A `HAVING` clause consists of boolean expressions; that is, expressions that evaluate to `true` or `false`, and may be built up using boolean expressions that are joined using `AND` and `OR`.

`HAVING` clauses are similar to `WHERE` clauses but they act on groups rather than individual records. Consequently the objects of comparison are not attribute values of records. Objects of comparison fall into two classes:

1. Attributes that remain constant in value through the group. In other words, attributes that appear in a `GROUP BY` clause.

2. Group (i.e., aggregate) functions; that is, functions applied to the records over the group.

Syntax

  `HAVING` | `|(b_expr) |b_expr |b_expr OR b_expr |b_expr AND b_expr |NOT b_expr` | `| | | | |`

  where:

  `b_expr` | `is a boolean expression. Details on the boolean expression is described below.`

Notes

1. If two boolean expressions are joined by an `AND`, both boolean expressions must be true in order for the whole boolean expression to be true.

2. If two boolean expressions are joined by an `OR`, either one or both boolean expressions must be true in order for the whole boolean expression to be true.

3. Pattern matching in `HAVING` clauses is the same as for `WHERE` clauses. See the entry for WHERE clauses.

4. A `HAVING` clause may contain boolean expressions of the form:

   |  |  | `|gp_expr [ | | |NULL gp_expr` | `|IS |[IS] NOT` | `|] gp_condition |` | `| | | |`

   A `gp_expr` is one of:

   |  |  | `|gp_attr |string |number |gp_function |(gp_expr) |CONVERT gp_expr [TO] data_type |CONVERT gp_expr [TO] GENERIC data_type |gp_expr CONVERT TO data_type |gp_expr CONVERT [TO] GENERIC data_type |gp_expr operator gp_expr |gp_expr operator |built_in_function |user_defined_function` | `| | | | | | | | | | | | |`

   A `gp_attr` is an attribute that appears in a `GROUP BY` clause. If the attribute appears in a `GROUP BY` clause, the value of the attribute for each group is used.

   A `gp_function` is a group function on an attribute that does not appear in a `GROUP BY` clause. For an attribute that does not appear in a `GROUP BY` clause, an aggregate function such as `COUNT`, `AVG`, `MAX`, `MIN` or `SUM` must be applied to the attribute over the group.

   Refer to the chapter Expressions for the `operators` and Functions for the `built_in_functions`.

   A `gp_condition` is:

   |  |  | `| | | | |` |
   `| = |!= | > | >= | <` | `| gp_expr | | | |`
   |  |  | `| | | | |`
   |  |  | `| | | | | |` |
   `|LIKE |MATCH |!MATCH |SMATCH | !SMATCH` | `| pattern | | | |`
   |  |  | `| | | | | |`
   |  |  | `|[ |` |
   `| = |!=` | `|] NULL |`
   |  |  | `| |`
   |  |  | `| |` | `BETWEEN gp_expr [` |
   `|EXCLUSIVE |INCLUSIVE` | `|] [AND] |`
   `gp_expr [` | `|EXCLUSIVE|] |INCLUSIVE|`
   | `| |`
   |  |  | `| |` | `RANGE gp_expr [` |
   `|EXCLUSIVE |INCLUSIVE` | `|] [TO] |`
   |  |  | `gp_expr [` | `|EXCLUSIVE|] |INCLUSIVE|`
   | `| |`

   `BETWEEN ... AND` is an alternative to `RANGE ... TO`. Values are inclusive by default and must be supplied in increasing order for any groups to meet the condition.

Example

1. To print the name and number of loans outstanding to employees whose names have `i` or `o` as the second letter, use:

          SELECT name, COUNT FROM loans GROUP BY name
             HAVING name match '?[io]*';

   which produces:

          name          COUNT (*)

          Jones         3
          Kilroy        1
          Mosca         2

2. To print the name and number of loans for those employees who have more than two loans, use:

          SELECT name, COUNT FROM loans GROUP BY name
             HAVING COUNT > 2;

   which produces:

          name          COUNT (*)

          Jones         3
          Wladislaw     3

3. To print the name and total outstanding in loans for those employees who have borrowed more than $200, use:

          SELECT name, SUM (amount) FROM loans GROUP BY name
             HAVING SUM (amount) > 200;

   which produces:

          name          SUM (amount)

          Jones         358.95
          Kilroy        250.00
          Mosca         350.00
          Scarlatti     275.00

4. To print average and total figures for those employees who have accumulated indebtedness of greater than $300 as the result of several outstanding loans averaging $150, use:

          SELECT name, AVG (amount), SUM (amount) FROM loans
             GROUP BY name HAVING AVG (amount) < 150 AND
             SUM (amount) > 300;

   which produces:

          name          AVG (amount)     SUM (amount)

          Jones         119.65           358.95