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