COUNT

Aggregate · Window

COUNT(*) includes every row. COUNT(value) ignores NULL; with multiple arguments only rows where all arguments are non-NULL contribute. COUNT never returns NULL.

Syntax

A window function keeps each input row and adds a result for that row. PARTITION BY groups rows with matching values; without it, all input rows form one group.

Star (aggregate)

count(*)

Values (aggregate)

count([DISTINCT] value [, value ...])

Window

count(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

RANGE frames with a numeric or time offset are not supported. Use unique ORDER BY values for calculations that advance one row at a time. DISTINCT is not supported in these window forms.

Window star

count(*) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

RANGE frames with a numeric or time offset are not supported. Use unique ORDER BY values for calculations that advance one row at a time. DISTINCT is not supported in these window forms.

Arguments

star, window star: Takes no arguments.

Values

Name	Type	Required	Description
value	BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT	Yes	Count rows where all supplied expressions are non-NULL. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point).

Window

Name	Type	Required	Description
value	BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT	Yes	Count non-NULL rows. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point).

Returns

Type: BIGINT
Possible values: A nonnegative integer.
NULL behavior: COUNT(*) includes every row. COUNT(value) ignores NULL; with multiple arguments only rows where all arguments are non-NULL contribute. COUNT never returns NULL.
Empty input: Returns 0 on an empty global aggregate or empty frame.

Properties

Property	Value
Deterministic	Yes
Supports DISTINCT	Partial (Grouped aggregation only; see the window limitations below.)
Supports window usage	Yes
Supports window frames	Partial (Use the ROWS forms shown here; RANGE offsets are not supported.)
Exact result	Yes

Usage notes

INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
Use DISTINCT in grouped aggregation only. It does not provide duplicate removal in a window call.

Examples

Sample data

id	k	v
1	a	2
2	a	2
3	a	4
4	a	NULL
5	b	6
6	b	8
7	c	NULL

All rows

WITH t("value") AS (VALUES (1), (CAST(NULL AS INTEGER)), (3)) SELECT count(*) AS result FROM t;

result
3

Non-NULL values

WITH t("value") AS (VALUES (1), (CAST(NULL AS INTEGER)), (3)) SELECT count("value") AS result FROM t;

result
2

Distinct values

WITH t("value") AS (VALUES (1), (1), (2), (CAST(NULL AS INTEGER))) SELECT count(DISTINCT "value") AS result FROM t;

result
2

Empty input

WITH t(v) AS (VALUES (1)) SELECT count(*) AS result FROM t WHERE FALSE;

result
0

Aggregate the sample values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT count(v) AS result FROM sample;

result
5

One result per group, including an all-NULL group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT k, count(v) AS result
FROM sample
GROUP BY k
ORDER BY k NULLS LAST;

k	result
a	3
b	2
c	0

DISTINCT removes repeated input values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT count(DISTINCT v) AS result FROM sample;

result
4

Window over all rows in each group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, count(v) OVER (PARTITION BY k) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	3
2	a	3
3	a	3
4	a	3
5	b	2
6	b	2
7	c	0

Running window

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, count(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	1
2	a	2
3	a	3
4	a	3
5	b	1
6	b	2
7	c	0

Sliding window with one preceding row

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, count(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	1
2	a	2
3	a	2
4	a	1
5	b	1
6	b	2
7	c	0

Only NULL values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT count(v) AS result FROM sample WHERE v IS NULL;

result
0

Empty filtered sample

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT count(v) AS result FROM sample WHERE id < 0;

result
0

COUNT star includes NULL rows

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT count(*) AS result FROM sample;

result
7

COUNT star in a window includes NULL rows

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, count(*) OVER (PARTITION BY k) AS result
FROM sample
ORDER BY id NULLS LAST;

id	result
1	4
2	4
3	4
4	4
5	2
6	2
7	1

Limitations

COUNT(DISTINCT …) is not supported as a window function, with or without ORDER BY.

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts

Syntax

Star (aggregate)

Values (aggregate)

Window

Window star

Arguments

Values

Window

Returns

Properties

Usage notes

Examples

Sample data

All rows

Non-NULL values

Distinct values

Empty input

Aggregate the sample values

One result per group, including an all-NULL group

DISTINCT removes repeated input values

Window over all rows in each group

Running window

Sliding window with one preceding row

Only NULL values

Empty filtered sample

COUNT star includes NULL rows

COUNT star in a window includes NULL rows

Limitations

​Syntax

​Star (aggregate)

​Values (aggregate)

​Window

​Window star

​Arguments

​Values

​Window

​Returns

​Properties

​Usage notes

​Examples

​Sample data

​All rows

​Non-NULL values

​Distinct values

​Empty input

​Aggregate the sample values

​One result per group, including an all-NULL group

​DISTINCT removes repeated input values

​Window over all rows in each group

​Running window

​Sliding window with one preceding row

​Only NULL values

​Empty filtered sample

​COUNT star includes NULL rows

​COUNT star in a window includes NULL rows

​Limitations

Syntax

Star (aggregate)

Values (aggregate)

Window

Window star

Arguments

Values

Window

Returns

Properties

Usage notes

Examples

Sample data

All rows

Non-NULL values

Distinct values

Empty input

Aggregate the sample values

One result per group, including an all-NULL group

DISTINCT removes repeated input values

Window over all rows in each group

Running window

Sliding window with one preceding row

Only NULL values

Empty filtered sample

COUNT star includes NULL rows

COUNT star in a window includes NULL rows

Limitations