COUNTIF

Aggregate · Window

Aliases: count_if FALSE and NULL conditions are ignored. Empty input or no true conditions returns 0.

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.

Aggregate

countif(condition)

Window within each group

countif(condition) OVER (PARTITION BY key)

Frame (window)

countif(condition) OVER (ORDER BY key NULLS LAST ROWS BETWEEN lower_bound AND upper_bound)

For ordered frames with no numeric offsets, rows with the same ORDER BY value are treated together. Use unique ORDER BY values when a running result must advance one row at a time.

Arguments

Aggregate and window

Name	Type	Required	Description
condition	BOOLEAN	Yes	Condition evaluated for each row. Only TRUE contributes to the count.

Returns

Type: BIGINT
Possible values: A nonnegative count of TRUE conditions, represented as a signed 64-bit integer.
NULL behavior: FALSE and NULL conditions are ignored. Empty input or no true conditions returns 0.
Empty input: 0, including an empty window frame.

Properties

Property	Value
Deterministic	Yes
Supports DISTINCT	No
Supports window usage	Yes
Supports window frames	Partial (Use the ROWS forms shown here; RANGE offsets are not supported.)
Exact result	Yes

Usage notes

Use an explicit frame when the calculation depends on which rows belong to the window. The examples specify ROWS frames and a unique row ID.

Examples

Sample data

k	id	flag
1	1	true
1	2	false
1	3	NULL
2	4	true
2	5	true
3	6	NULL

Basic aggregate

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT countif(flag) AS result FROM sample;

result
3

Grouped aggregate

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT k, countif(flag) AS result FROM sample GROUP BY k ORDER BY k NULLS LAST;

k	result
1	1
2	2
3	0

Window within each group

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, countif(flag) OVER (PARTITION BY k) AS result FROM sample ORDER BY id NULLS LAST;

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

Running frame

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, countif(flag) OVER (ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result FROM sample ORDER BY id NULLS LAST;

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

Sliding frame

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT id, countif(flag) OVER (ORDER BY id NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result FROM sample ORDER BY id NULLS LAST;

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

All-NULL group

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT countif(flag) AS result FROM sample WHERE k = 3;

result
0

Empty input

WITH sample(k, id, flag) AS (VALUES (1, 1, TRUE), (1, 2, FALSE), (1, 3, CAST(NULL AS BOOLEAN)), (2, 4, TRUE), (2, 5, TRUE), (3, 6, CAST(NULL AS BOOLEAN)))
SELECT countif(flag) AS result FROM sample WHERE FALSE;

result
0

Limitations

COUNT_IF and COUNTIF do not accept DISTINCT.
RANGE frames are supported only when both ends are unbounded, covering the entire group. Use an explicit ROWS frame for ordered calculations.
For ordered frames with no numeric offsets, rows with the same ORDER BY value are treated together. Use unique ORDER BY values when a running result must advance one row at a time.

count

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts

Syntax

Aggregate

Window within each group

Frame (window)

Arguments

Aggregate and window

Returns

Properties

Usage notes

Examples

Sample data

Basic aggregate

Grouped aggregate

Window within each group

Running frame

Sliding frame

All-NULL group

Empty input

Limitations

​Syntax

​Aggregate

​Window within each group

​Frame (window)

​Arguments

​Aggregate and window

​Returns

​Properties

​Usage notes

​Examples

​Sample data

​Basic aggregate

​Grouped aggregate

​Window within each group

​Running frame

​Sliding frame

​All-NULL group

​Empty input

​Limitations

​Related functions

Syntax

Aggregate

Window within each group

Frame (window)

Arguments

Aggregate and window

Returns

Properties

Usage notes

Examples

Sample data

Basic aggregate

Grouped aggregate

Window within each group

Running frame

Sliding frame

All-NULL group

Empty input

Limitations

Related functions