COUNT
Counts the number of rows or not NULL values.
Syntax
COUNT([ DISTINCT ] <expr>)
Parameter | Description |
---|---|
<expr> | Valid values for the expression include column names (or * for counting all columns) or functions that return a column name. When DISTINCT is being used, counts only the unique number of rows with no NULL values. |
COUNT(*)
returns a total count of all rows in the table, whileCOUNT(<column_name>)
returns a count of non-NULL rows in the specified<column_name>
.By default,
COUNT(DISTINCT)
returns approximate results. To get a precise result, with a performance penalty, useSET firebolt_optimization_enable_exact_count_distinct=1;
Example
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;
Returns: 8
A 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;
Returns: 5