GROUP BY clause

Syntax

GROUP BY { column | gettimestamp } [, ...]

Usage

The optional GROUP BY clause specifies a list of one or more column references. The list can also contain a GETTIMESTAMP (Scalar Function) function. This clause has the following effect on the behavior of aggregators, window contents, and the OUTPUT clause, where combination refers to a unique combination of values in the list of columns and, optionally, the timestamp referenced by the GROUP BY clause.

• If a GROUP BY clause is present, aggregate functions are executed separately for every combination. In the absence of a GROUP BY clause, aggregation is performed once for each aggregate function for every group of rows received by the clause that contains the aggregate function.

• If a GROUP BY clause is present in a query that includes one or more unnamed window policies (as defined by the KEEP subclause in the FROM clause) all the window policies are implemented separately for every combination, effectively resulting in the creation of a separate window for every combination. (The GROUP BY clause actually adds one or more implicit PER subclauses to each KEEP clause.) This default behavior can be overridden by adding one or more explicit PER clauses or an UNGROUPED clause to the window policies in question. See the description of the KEEP clause for more information.

  In the absence of a GROUP BY clause, a single policy is implemented for every KEEP clause.

• The presence of a GROUP BY clause affects the behavior of the OUTPUT clause, causing it to output separate rows for every combination. See OUTPUT for more details.

See Also

• Database Statement

• Query Statement

• Remote Procedure Statement

• HAVING

• KEEP

• OUTPUT

• GETTIMESTAMP (Scalar Function)

Examples

The following example calculates the average price separately for each symbol in the NamedWindow.Symbol column.

INSERT INTO OutStream
SELECT Symbol AS Symbol, AVG(Price) AS Average
FROM NamedWindow
GROUP BY Symbol;

This example calculates the average prices as in the previous example, but uses an unnamed window that retains ten rows for each value in the Symbol column.

INSERT INTO OutStream
SELECT Symbol AS Symbol, AVG(Price) AS Average
FROM InStream KEEP 10 ROWS
GROUP BY Symbol;