Skip to main content

Celonis Product Documentation

COUNT_TABLE
Description

This aggregate function calculates the number of rows in 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 in a table, rather than the number of rows in 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 in 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. You can find more details about the common table and join specifics in EMS in Join functionality.

Examples

[1]

Count the number of rows in 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 in 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 in 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

Warning

The aggregation function COUNT is applied on a column from table "Table1" which has a 1:N relationship to the common table "TableN". This means that one input value can contribute to the aggregation result multiple times. For more information search for "Join functionality" in PQL documentation.

See also: