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 thatCOUNT_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: | ||||||||||||||||||
| ||||||||||||||||||
|
[2] Count the number of rows in two joined tables. The output is grouped by column1. Within each group, | ||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||
|
[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: | ||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||
|
[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 The third column in this example shows that the | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|