count ​

Counts the number of rows or non-NULL values.

CoordinatorDB supports:

• count(expr) and count(DISTINCT expr).
• count() and count(*).

Details

count(*), count(), and count(1) count every admitted row, including rows containing NULL. count(expr) evaluates the expression for each row and counts its non-NULL results. DISTINCT counts distinct non-NULL values using SQL comparison and the applicable collation.

Use FILTER (WHERE condition) to select which rows an aggregate counts, or count_if to count rows satisfying a condition.

Syntax

count()
count(*)
count(expr)
count(DISTINCT expr)

Arguments

• expr — Optional. Any SQL expression. When supplied, only its non-NULL results contribute to the count.

Returned value

A nonnegative signed INTEGER. Empty input returns zero.

Examples

Basic row count

CREATE TABLE t (num UInt8);
INSERT INTO t VALUES (1), (1), (2), (2), (3);

SELECT count() FROM t FORMAT csv;

5

COUNT(DISTINCT) example

SELECT count(DISTINCT num) FROM t FORMAT csv;

3

Counting missing values

INSERT INTO t VALUES (NULL);

SELECT count(*), count(num), count(*) FILTER (WHERE num IS NULL)
FROM t
FORMAT csv;

6,5,1