Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

Standard aggregation

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

Standard aggregation functions group input rows together and calculate a single value for each group.

Which rows are aggregated into a single value is defined by the grouper columns. Every column within one query that is not an aggregation is a grouper column. If no grouper is defined, all values are aggregated into one group.

The fact that grouper columns are defined implicitly is a main difference to SQL. While all other aggregation behaviour sticks to SQL, PQL aggregations have no 'GROUP BY' clause.

Aggregations take filters and selections into account. Values which are filtered out are not part of the result. Therefore, if a filter or a selection is changed every aggregation is recalculated.

Implicit joining

Please be aware that implicit joining of all columns in the aggregation query is done before the actual aggregations are performed.

Example

[1] This is an example for a standard aggregation. Column1 is the grouper column. The distinct values of "Table1.Country" define the groups in the result. COUNT("Table1"."Values") in Column2 defines how the values of each group are aggregated. Here, all rows in one group are counted.

Query

Column1

"Table1"."Country"

Column2

COUNT ( "Table1"."Values" )

Input

Output

Table1

Country : STRING

Values : INT

'US'

3

'DE'

10

'DE'

5

'FR'

5

'US'

4

'US'

3

Result

Column1 : STRING

Column2 : INT

'DE'

2

'FR'

1

'US'

3