Skip to main content

Celonis Product Documentation

COUNT_TABLE
Description

This aggregate function calculates the number of rows of a specified table.

Syntax
 COUNT_TABLE ( table )
Remarks

COUNT_TABLE has some special properties which lead to different behavior compared to the COUNT and COUNT DISTINCT functions:

  • COUNT_TABLE returns the number of rows of a table, rather than the number of rows of a column. The difference is that COUNT_TABLE also counts rows which contain NULL values, while COUNT and COUNT DISTINCT ignore NULL values.

  • COUNT_TABLE will return the original number of rows of the specified table, even if the common table of the query is different from the specified table. However, rows without a join partner will still not be counted in that case. COUNT_TABLE is therefore equivalent to the result of COUNT DISTINCT on a non-null unique ID column of the table. More about the common table and join specifics in Celonis can be found in Join functionality.

Examples

[1]

Count the number of rows of a single table:

Query

Column1

         COUNT_TABLE ( "TableN" )
        

Input

Output

TableN

column1 : string

column2 : int

'A'

1

'A'

1

'A'

3

'B'

1

'D'

2

Result

Column1 : int

5

[2]

Count the number of rows of two joined tables. The output is grouped by column1. Within each group, COUNT_TABLE counts the number of rows originating from the requested table:

Query

Column1

         "TableN"."column1"
        

Column2

         COUNT_TABLE ( "TableN" )
        

Column3

         COUNT_TABLE ( "Table1" )
        

Input

Output

Table1

ID : string

column3 : string

'A'

'x'

'B'

'y'

'C'

'z'

TableN

column1 : string

column2 : int

'A'

1

'A'

1

'A'

3

'B'

1

'D'

2

Foreign Keys

TableN.column1

Table1.ID

Result

Column1 : string

Column2 : int

Column3 : int

'A'

3

1

'B'

1

1

'D'

1

0

[3]

Count the number of rows of two joined tables without any grouping. Due to the missing join partner for the third row of Table1 in the join with TableN, this row is not counted. TableN's last row also does not have a join partner with Table1, but this is still counted, because it remains in the result due to the left outer join that is performed:

Query

Column1

         COUNT_TABLE ( "TableN" )
        

Column2

         COUNT_TABLE ( "Table1" )
        

Input

Output

Table1

ID : string

column3 : string

'A'

'x'

'B'

'y'

'C'

'z'

TableN

column1 : string

column2 : int

'A'

1

'A'

1

'A'

3

'B'

1

'D'

2

Foreign Keys

TableN.column1

Table1.ID

Result

Column1 : int

Column2 : int

5

2

[4]

This is the same example scenario as above, but with additional COUNT DISTINCT and COUNT statements. The common table of this query is still TableN. This is why the COUNT of Table1.ID returns 4, which is the number of non-null rows after executing the join with the common table. As shown before, COUNT_TABLE of Table1 returns a different result, as it is only influenced by the common table regarding missing join partners.

The third column in this example shows that the COUNT DISTINCT on the ID column of Table1 indeed returns the same result as COUNT_TABLE of Table1:

Query

Column1

         COUNT_TABLE ( "TableN" )
        

Column2

         COUNT_TABLE ( "Table1" )
        

Column3

         COUNT ( DISTINCT "Table1"."ID" )
        

Column4

         COUNT ( "Table1"."ID" )
        

Input

Output

Table1

ID : string

column3 : string

'A'

'x'

'B'

'y'

'C'

'z'

TableN

column1 : string

column2 : int

'A'

1

'A'

1

'A'

3

'B'

1

'D'

2

Foreign Keys

TableN.column1

Table1.ID

Result

Column1 : int

Column2 : int

Column3 : int

Column4 : int

5

2

2

4

See also: