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 expressions from the input window or stream. However, an expression cannot use aggregate functions.

Usage

It combines one or more result rows into a single row of output. A GROUP BY clause is used when the query result contains aggregate functions to specify what expressions to perform the aggregation operation on.

When a GROUP BY clause is used in a query, the associated window needs to have a primary key that is deduced by the compiler. 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.

Use the actual expression in the GROUP BY clause, rather than the alias for that expression which prevents the project from compiling. For example, use the expression T.Symbol instead of an alias like Symbol.

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 FILTER rank() < 10
GROUP BY T.Symbol
GROUP ORDER BY T.Volume DESC
HAVING max(T.Price) > 100 AND T.Symbol ='IBM';