Skip to main content

Celonis Product Documentation

COUNT DISTINCT
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 the values of a group are NULL, the result for this group is 0.

COUNT DISTINCT over multiple columns

UNIQUE_ID can be used inside COUNT DISTINCT to count the number of unique combinations of values from different columns.

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 of distinct values 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 distinct 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

[4]

UNIQUE_ID can be used to count the distinct number of unique combinations of values from different columns. This example shows a list of six vendors together with the city and country of their headquarters. We want to count the number of different cities our vendors come from. Since just the city names are not unique, it is required to count unique combinations of city name and country. We can use UNIQUE_ID to give a unique ID to each of those combinations, and apply COUNT DISTINCT on these unique IDs:

Query

Column1 

         COUNT ( DISTINCT UNIQUE_ID ( "Vendors"."City" , "Vendors"."Country" ) )

Input

Output

Vendors

Name : string

City : string

Country : string

'A'

'Munich'

'DE'

'B'

'Berlin'

'DE'

'C'

'Berlin'

'NH'

'D'

'Berlin'

'DE'

'E'

'Bristol'

'UK'

'F'

'Bristol'

'TN'

Result

Column1 : int

5

See also: