ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 507
SQL Statements S - Z
SELECT
when aggregate functions are specified in the select list and you want to
apply the function to groups of rows. You can specify as many as 1023
columns, unless the select list contains an aggregate function with the
DISTINCT option, in which case you can specify as many as 254 columns.
The syntax for the group column list in the GROUP BY clause follows:
{ [ Owner. ] TableName.
CorrelationName. ] ColumnName }[,...]
When you use the GROUP BY clause, the select list can contain only
aggregate functions and columns referenced in the GROUP BY clause. If
the select list contains an *, a
TableName.* , or an Owner.TableName.*
construct, then the GROUP BY clause must contain all columns that the *
includes. Specify the grouping column names in order from major to minor.
Null values are considered equivalent in grouping columns. If all other
columns are equal, all nulls in a column are placed in a single group.
If the GROUP BY clause is omitted, the entire query result table is treated
as one group.
HAVING The HAVING clause specifies a test to be applied to each group. Any group
for which the result of the test is false or unknown is excluded from the
query result. This test, referred to as
SearchCondition2 , can be a
predicate containing either an aggregate function or a column named in
the GROUP BY clause.
Each subquery in
SearchCondition2 is effectively checked for each group
created by the GROUP BY clause, and the result is used in the application
of
SearchCondition2 to the given group. If any executed subquery
contains an outer reference to a column, then the reference is to the values
of that column in the given group. Only grouping columns can be used as
outer references in a subquery in
SearchCondition2 .
SQL Syntax — SelectList
{*
[ Owner. ] Table. *
Correlation.Name*
Expression
[[ Owner. ] Table. ] ColumnName
CorrelationName.ColumnName }[,...]
Parameters — SelectList
* includes, as columns of the result table, all columns of all tables and views
specified in the FROM clause.
[
Owner .] Table .* includes all columns of the specified table or view in the result.
CorrelationName .* includes all columns of the specified table or view in the result. The
correlation name is a synonym for the table or view as defined in the
FROM clause.
Expression produces a single column in the result table; the result column values are