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

Description

This function calculates the number of distinct elements per group. Count Distinct can be applied on any data type.

Syntax

COUNT ( DISTINCT table.column )

Null handling

Null values are not counted. If all values of a group are null, the result for this group is 0.

Examples


Count distinct elements of the column.

Query
Column1
COUNT (DISTINCT "Table1"."Column1" )
Input
Table1
Column1 : INT
3
1
1
2
Output
Result
Column1 : INT
3



Count the number grouped by country.

Query
Column1
"Table1"."Country"
Column2
COUNT (DISTINCT "Table1"."Values" )
Input
Table1
Country : STRINGValues : INT
'US'
3
'DE'
10
'DE'
5
'FR'
5
'US'
4
'US'
3
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
2
'FR'
1
'US'
2



Count the number of values mixed with nulls, grouped by country.

Query
Column1
"Table1"."Country"
Column2
COUNT (DISTINCT "Table1"."Values" )
Input
Table1
Country : STRINGValues : INT
'FR'
10
'FR'
null
'DE'
null
Output
Result
Column1 : STRINGColumn2 : INT
'DE'
0
'FR'
1


  • No labels