The GROUP BY and HAVING Clauses
The GROUP BY clause determines grouping of the result table for the application of set functions specified in the SELECT clause.
The HAVING clause restricts selection of groups in the same way that a WHERE clause restricts selection of rows.
The GROUP BY and HAVING clauses have the syntax:
If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.
The rows of the intermediate result set are (conceptually) arranged in groups, where all values in the grouping column(s) are identical within each group.
Each group is reduced to a single row in the final result of the select-specification.
The following example lists the number of rooms for each room type in each hotel:SELECT H.NAME, R.ROOMTYPE, COUNT(*) FROM HOTEL H, ROOMS R WHERE H.HOTELCODE = R.HOTELCODE GROUPBY H.NAME, R.ROOMTYPE
If a GROUP BY clause is not specified, the SELECT list must either be a list that does not include any set functions or a list of set functions and optional literal expressions.
The search condition in the HAVING clause defines restrictions on the values in the elements of the SELECT list. Column references in the search condition of the having clause must identify a grouping column, or be used in set functions, or be outer references.
The following example is similar to the example above, but room types with less than 10 occurrences are not listed.SELECT H.NAME, R.ROOMTYPE, COUNT(*) FROM HOTEL H, ROOMS R WHERE H.HOTELCODE = R.HOTELCODE GROUP BY H.NAME, R.ROOMTYPE HAVING COUNT(*) >= 10;
Most commonly, HAVING is used together with GROUP BY, in which case the search conditions relate either values in grouping columns or results of set functions to expressions.
If the HAVING clause is used without a GROUP BY clause, all rows in the result table are treated as a single group. In this case, the HAVING clause must refer to a set function (since there are no grouping columns).
The GROUP BY or HAVING clause may not be specified in a select-specification whose FROM clause names a view that uses GROUP BY or HAVING, or in a select-specification used in a basic predicate.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40