Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

COUNT DISTINCT

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

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

[1] Count distinct elements of the column.

Query

Column1

COUNT ( DISTINCT "Table1"."Column1" )

Input

Output

Table1

Column1 : INT

3

1

1

2

Result

Column1 : INT

3

[2] Count the number grouped by country.

Query

Column1

"Table1"."Country"

Column2

COUNT ( DISTINCT "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'

2

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

Query

Column1

"Table1"."Country"

Column2

COUNT ( DISTINCT "Table1"."Values" )

Input

Output

Table1

Country : STRING

Values : INT

'FR'

10

'FR'

null

'DE'

null

Result

Column1 : STRING

Column2 : INT

'DE'

0

'FR'

1