Counts the number of rows or not NULL values.
COUNT([ DISTINCT ] <expr>)
| ||Valid values for the expression include column names or functions that return a column name. When |
COUNT(*) returns a total count of all rows in the table, while
COUNT(<column_name>) returns a count of non-NULL rows in the specified
COUNT(DISTINCT)returns approximate results. If you require a precise result (with a performance penalty), please contact Firebolt Support through the Help menu support form.
For this example, we’ll create a new table
number_test as shown below.
CREATE DIMENSION TABLE IF NOT EXISTS number_test ( num TEXT ); INSERT INTO number_test VALUES (1), (1), (2), (3), (3), (3), (4), (5);
Doing a regular
COUNT returns the total number of rows in the column. We inserted 8 rows earlier, so it should return the same number.
SELECT COUNT(num) FROM number_test;
COUNT(DISTINCT) function on the same column returns the number of unique rows. There are five unique numbers that we inserted earlier.
SELECT COUNT(DISTINCT num) FROM number_test;
To understand the difference between
COUNT(DISTINCT pk) with exact precision enabled and using default approximation, consider a table,
count_test with 8,388,608 unique
pk values. The
APPROX_COUNT_DISTINCT function returns the same approximate results as the
COUNT(DISTINCT) function with exact precision disabled, so we can see the difference between these methods with the following example.
SELECT COUNT(DISTINCT pk) as count_distinct, APPROX_COUNT_DISTINCT(pk) as approx_count FROM count_test;
Assuming 8,388,608 unique pk values, we will see results like:
' +----------------+--------------+ ' | count_distinct | approx_count | ' +----------------+--------------+ ' | 8,388,608 | 8,427,387 | ' +----------------+--------------+