1.0

GROUP BY Clause
Group a result into subsets that have matching values for one or more columns.
Syntax
GROUP BY column-name [ , column-name ] *
Description
A GROUP BY clause, part of a SelectExpression, groups a result into subsets that have matching values for one
or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs
are considered equivalent for grouping purposes.
You typically use a GROUP BY clause in conjunction with an aggregate expression.
column-name must be a column from the current scope of the query; there can be no columns from a query block
outside the current scope. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in
the outer query.
SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.
Example
-- find the quantity of units held by customer grouped by
customer ID
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID
SELECT MAX(F.QTY), C.CID FROM TRADE.PORTFOLIO F,
TRADE.CUSTOMERS C
WHERE F.CID = C.CID GROUP BY C.CID
SELECT (AVG(SUBTOTAL/QTY)) FROM TRADE.PORTFOLIO F WHERE F.SID
= ? AND F.TID =? AND F.QTY <> 0
HAVING Clause
Restrict the results of a GROUP BY in a SelectExpression.
Syntax
HAVING searchCondition
Description
A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied
to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP
BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer
directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates,
and special registers.
The searchCondition, which is a specialized booleanExpression, can contain only grouping columns (see GROUP
BY clause), columns that are part of aggregate expressions, and columns that are part of a subquery. For example,
the following query is illegal, because the column AVAILQTY is not a grouping column, it does not appear
within an aggregate, and it is not within a subquery.
Aggregates in the HAVING clause do not need to appear in the SELECT list. If the HAVING clause contains
a subquery, the subquery can refer to the outer query block if and only if it refers to a grouping column.
vFabric SQLFire User's Guide490
vFabric SQLFire Reference