Skip to main content

Celonis Product Documentation

SUM
Description

SUM calculates the sum over an INT or FLOAT column.

The data type of the result is the same as the input column data type. So the sum of an INT column will be of type INT.

Syntax
 SUM ( table.column )
NULL handling

NULL values are ignored, so they do not influence the result. If all the values of a group are NULL, the result for this group is also NULL.

Example

[1]

Sum of small integers.

Query

Column1

         SUM ( "Table1"."Column1" )
        

Input

Output

Table1

Column1 : int

Column2 : int

1

2

3

4

Result

Column1 : int

4

Examples of SUM DISTINCT equivalent

[2]

This example shows how PQL operators can be used to obtain the equivalent behavior of SQL's SUM DISTINCT. CASE WHEN is used in combination with INDEX_ORDER to select only the first occurrence of a value in the column we want to aggregate. We can then use SUM to obtain the final result.

Query

Column1

         SUM ( CASE WHEN INDEX_ORDER ( "Table1"."Aggregate" , PARTITION BY ( "Table1"."Aggregate" ) ) = 1 THEN "Table1"."Aggregate" ELSE NULL END )
        

Input

Output

Table1

Aggregate : int

1

1

3

2

Result

Column1 : int

6

[3]

If there are filters which need to be taken into account, the column we want to sum distinct has to be wrapped in a FILTER_TO_NULL operator inside the PARTITION BY clause.

Query

Filter

         FILTER "Table1"."Aggregate" < 3;
        

Column1

         SUM ( CASE WHEN INDEX_ORDER ( "Table1"."Aggregate" , PARTITION BY ( FILTER_TO_NULL ( "Table1"."Aggregate" ) ) ) = 1 THEN "Table1"."Aggregate" ELSE NULL END )
        

Input

Output

Table1

Aggregate : int

1

1

3

2

Result

Column1 : int

3

[4]

Any grouper columns we want to take into account also need to be added to the PARTITION BY clause of the INDEX_ORDER operator.

Query

Column1

         "Table1"."Grouper"
        

Column2

         SUM ( CASE WHEN INDEX_ORDER ( "Table1"."Aggregate" , PARTITION BY ( "Table1"."Grouper" , FILTER_TO_NULL ( "Table1"."Aggregate" ) ) ) = 1 THEN "Table1"."Aggregate" ELSE NULL END )
        

Input

Output

Table1

Grouper : string

Aggregate : int

'001'

1

'001'

1

'002'

3

'002'

2

Result

Column1 : string

Column2 : int

'001'

1

'002'

5

See also: