GROUP BY Clause

Specifies the expressions on which to perform an aggregation operation.

Syntax

GROUP BY expression1 [, expression2 ...]

Components

expression

An expression using constants, which can contain one or more columns from the input window or stream. However, an expression cannot use aggregate functions.

Usage

It combines one or more input rows into a single row of output. Typically, GROUP BY is used for aggregation. The query will contain a GROUP BY to specify how to group the inputs, and one or more of the column expressions in the SELECT clause will use an aggregate function to compute aggregate values for the group.

When a GROUP BY clause is used in a query, the compiler will deduce the primary key based on the group by expression(s). If more than one column has the same expression, the first column is used if it has not already been matched with a GROUP BY expression.

Note: Every expression in the GROUP BY clause must also be in at least one SELECT column expression.

Note that the GROUP BY clause must reference input columns directly. It cannot use aliases defined in the local SELECT clause.

Example

The GROUP BY clause collects together the rows according to T.Symbol:

CREATE WINDOW Window1 SCHEMA (Symbol STRING, MaxPrice INTEGER)
PRIMARY KEY DEDUCED
KEEP ALL
AS 
SELECT T.Symbol, max(T.Price) MaxPrice
FROM Trades T
GROUP BY T.Symbol