GROUP BY clause

The GROUP BY clause is used to group the result of a SELECT statement by the expressions at the specified indices. For example, GROUP BY 1, 2 groups the result by the first and second expression. All rows that share the same values for the grouped expressions are condensed into a single row.

The GROUP BY clause is often used with aggregate functions, for example COUNT, MAX, MIN, and AVG. Read more in section Aggregate functions. An aggregate function is computed across all rows of each group and returns a separate value for each group.

The GROUP BY clause is optional. If the GROUP BY clause is not present, then the following applies:

• If there are aggregate and non-aggregate expressions in the SELECT statement, then the result is automatically grouped by any non-aggregate expressions.

• If there are only aggregate expressions in the SELECT statement, then the result is a single group comprising all the selected rows.

To specify the rows to be considered for the aggregation, you can apply the FILTER clause to the aggregate function.

If the GROUP BY clause is present, then the following applies:

• You must group by all expressions in the SELECT statement that are not encapsulated by an aggregate function. Exception: The ungrouped expression is functionally dependent from a grouped expression (see example 2 below).

• You can't group by an expression that contains an aggregate function.

Syntax:

SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM table
GROUP BY index [, ...]

Parameter	Description
index [, ...]	The 1-based indexing of the expressions in the SELECT clause that are not encapsulated within an aggregate function. The expression relates either to a case-level attribute, for example "city", or to an event-level attribute, for example "event-name". In the first case, the result is grouped by cities. In the second case, the result is grouped by the list of identical sequences of events. This can be used to identify process variants.
aggregate_function	An aggregate function such as the SUM, COUNT, MIN, MAX, or AVG function.
aggregate_expression	The column or expression that the aggregate function is used on.
table	The process table or view from which you want to retrieve records, referenced by explicit Process ID or the alias THIS_PROCESS.

Example 1: Case with GROUP BY on multiple columns

SELECT city, region, COUNT(case_id)
FROM THIS_PROCESS
GROUP BY 1, 2

GROUP BY 1, 2 groups by the expressions in the SELECT statement which are not encapsulated by an aggregation function. Since this SELECT statement contains two expressions (city and region), the GROUP BY index must refer to both expressions. GROUP BY 1 or GROUP BY 2 is not valid in this case.

Example 2: Case with GROUP BY

To get the count of case ids with actual order amounts and order amounts multiplied 2.50 times, run the following query.

SELECT "Order Amount", ("Order Amount"*2.50) AS "Orders", COUNT(case_id) AS "No.of Cases"
FROM THIS_PROCESS
GROUP BY 1, 2

Since the second expression order_amount*2.50 is functionally dependent to the first colum order_amount, GROUP BY 1 is valid in this case.

The query returns the following result.