The HAVING clause of a SELECT statement qualifies or disqualifies specific rows from the query selection. The rows that qualify are those for which the condition-expression is true. The condition-expression is a series of logical tests (predicates) which can be linked by the AND and OR logical operators. For further details, see the WHERE clause.
The HAVING clause is like a WHERE clause that can operate on groups, rather than on the full data set. Thus, in most cases, the HAVING clause is used either with an aggregate function using the %AFTERHAVING keyword, or in combination with a GROUP BY clause, or both.
A HAVING clause often serves to compare aggregates of sub-populations against aggregates for an entire population.
Specifying a Field
A field specified in a HAVING clause condition-expression or an %AFTERHAVING keyword expression must be specified as a field name or an aggregate function. You cannot specify a field or aggregate function by column number. You cannot specify a field or aggregate function by column alias; attempting to do so generates an SQLCODE -29 error. However, you can use a subquery to define a column alias, then use this alias in the HAVING clause. For example:
The HAVING clause selects which rows to return. By default, this row selection does not determine the value of aggregate functions in the select-item list. This is because the HAVING clause is parsed after aggregate functions in the select-item list.
In the following example, only those rows with Age > 65 are returned. But the AVG(Age) is calculated based on all rows, not just those selected by the HAVING clause:
A HAVING clause can be used in a query that only returns aggregate values:
Aggregate Threshold: The HAVING clause uses an aggregate threshold to determine whether to return 1 row (containing the query aggregate values) or 0 rows. Thus you can use a HAVING clause to only return an aggregate calculation when an aggregate threshold is achieved. The following example only returns an average of the Age values for all rows in the table when there are at least 100 rows in the table. If there are less than 100 rows, the average of the Age values for all rows might not be deemed meaningful, and therefore should not be returned:
Multiple Rows: A HAVING clause with an aggregate function and no GROUP BY clause returns the number of rows that fulfill the HAVING clause condition. The aggregate function value is calculated based on all of the rows in the table:
This is in contrast to a WHERE clause with an aggregate function, which returns one row. The aggregate function value is calculated based on rows that fulfill the WHERE clause condition:
The %AFTERHAVING keyword can be used with an aggregate function in the select-item list to specify that the aggregate operation is to be performed after the HAVING clause condition is applied.
The %AFTERHAVING keyword only gives meaningful results if both of the following considerations are met:
The select-item list must contain at least one item that is a non-aggregate field reference. This field reference may be to any field in any table specified in the FROM clause, a field referenced using an implicit join (arrow syntax), the %ID alias, or an asterisk (*).
The following example uses a HAVING clause with a GROUP BY clause to return the state average age, and the state average age for people that are older than the average age for all rows in the table. It also uses a subquery to return the average age for all rows in the table:
Note that SQL defines comparison operations in terms of collation: the order in which values are sorted. Two values are equal if they collate in exactly the same way. A value is greater than another value if it collates after the second value. String data type field collation is based on the field’s default collation. By default, it is not case-sensitive. Thus, a comparison of two string field values or a comparison of a string field value with a string literal is (by default) not case-sensitive. For example, if Home_State field values are uppercase two-letter strings:
'MA' = Home_State
TRUE for values MA.
'ma' = Home_State
TRUE for values MA.
'VA' < Home_State
TRUE for values VT, WA, WI, WV, WY.
'ar' >= Home_State
TRUE for values AK, AL, AR.
Note, however, that a comparison of two literal strings is case-sensitive: WHERE 'ma'='MA' is always FALSE.
This is equivalent to a paired greater than or equal to and less than or equal to. The following example uses a BETWEEN predicate. It returns one record for each Age between 18 and 35, inclusive of 18 and 35:
evaluates true if Home_State equals any of the values inside the parenthetical list. The list elements can be constants or expressions. Collation applies to the IN comparison as it applies to an equality test. By default, IN comparisons use the collation type of the field definition; by default string fields are defined as SQLUPPER, which is not case-sensitive.
When dates or times are used for IN predicate equality comparisons, the appropriate data type conversions are automatically performed. If the HAVING clause field is type TimeStamp, values of type Date or Time are converted to Timestamp. If the HAVING clause field is type Date, values of type TimeStamp or String are converted to Date. If the HAVING clause field is type Time, values of type TimeStamp or String are converted to Time.
The following examples both perform the same equality comparisons and return the same data. The GROUP BY field specifies to return only one record for each successful equality comparison. The DOB field is of data type Date:
The %INLIST predicate can be used to perform an equality comparison on the elements of a list structure. %INLIST uses EXACT collation. Therefore, by default, %INLIST string comparisons are case-sensitive. For further details on list structures, see the SQL $LIST function.
The following example uses %INLIST to match a string value to the elements of the FavoriteColors list field:
The Caché %STARTSWITH comparison operator permits you to perform partial matching on the initial characters of a string or numeric. The following example uses %STARTSWITH. It selects by age, then returns a record for each Name that begins with S:
The Contains operator is the open bracket symbol: [. It permits you to match a substring (string or numeric) to any part of a field value. The comparison is always case-sensitive. The following example uses the Contains operator in a HAVING clause to select those records in which the Home_State value contains a K, and then do an %AFTERHAVING count on those states:
The FOR SOME predicate of the HAVING clause determines whether or not to return a result set based on a condition test of one or more field values. This predicate has the following syntax:
FOR SOME (table[AS t-alias]) (fieldcondition)
FOR SOME specifies that fieldcondition must evaluate to true; at least one of the field values must match the specified condition. table can be a single table or a comma-separated list of tables, and can optionally take a table alias. fieldcondition specifies one or more conditions for one or more fields within the specified table. Both the table argument and the fieldcondition argument must be delimited by parentheses.
The following example shows the use of the FOR SOME predicate:
These three predicates allow you to perform pattern matching.
LIKE allows you to pattern match using literals and wildcards. Use LIKE when you wish to return data values that contain a known substring of literal characters, or contain several known substrings in a known sequence. LIKE uses the collation of its target for letter case comparisons.
%MATCHES allows you to pattern match using literals, wildcards, and lists and ranges. Use %MATCHES when you wish to return data values that contain a known substring of literal characters, or contain one or more literal characters that fall within a list or range of possible characters, or contain several such substrings in a known sequence. %MATCHES uses EXACT collation for letter case comparisons.
%PATTERN allows you to specify a pattern of character types. For example, '1U4L1",".A' (1 uppercase letter, 4 lowercase letters, one literal comma, followed by any number of letter characters of either case). Use %PATTERN when you wish to return data values that contain a known sequence of character types. %PATTERN is especially useful when the data value is unimportant, but the character type format of those values is significant. %PATTERN can also specify known literal characters. It uses EXACT collation for literal comparisons, which are always case-sensitive.
To perform a comparison with the first characters of a string, use the %STARTSWITH predicate.
The following example returns a row for each state that has at least one person under the age of 21. For each row it returns the average, minimum, and maximum ages of all people in the state.
The following example returns a row for each state that has at least one person under the age of 21. For each row it returns the average, minimum, and maximum ages of all people in the state. Using the %AFTERHAVING keyword, it also returns the average age of those people in the state under the age of 21 (AvgYouth), and the age of the oldest person in the state under the age of 21 (OldestYouth).