ClickHouse countIf Function: Conditional Counting with Examples

The countIf function in ClickHouse counts the number of rows where a UInt8 (boolean) condition evaluates to true. It is the count aggregate combined with ClickHouse's -If aggregate combinator, which lets you express conditional counts inline without a CASE WHEN or subquery. countIf runs in a single pass over the data and is typically faster than sum(if(cond, 1, 0)). Returns UInt64.

Syntax

countIf(cond)
countIf(value, cond)

The single-argument form is the most common. The two-argument form countIf(value, cond) counts rows where both value is non-NULL and cond is true, mirroring the behavior of count(value) with the -If combinator. See the official ClickHouse aggregate combinators reference.

Parameters

Return type: UInt64.

Examples

Counting by category in a single pass

SELECT
    country,
    countIf(age >= 18) AS adults,
    countIf(age < 18)  AS minors,
    countIf(age IS NULL) AS unknown_age
FROM users
GROUP BY country;

All three conditional counts share one scan of the users table - much faster than running three WHERE-filtered queries and joining.

HTTP status breakdown

SELECT
    toStartOfHour(timestamp) AS hour,
    count()                                       AS total,
    countIf(status_code >= 500)                   AS server_errors,
    countIf(status_code BETWEEN 400 AND 499)      AS client_errors,
    countIf(status_code BETWEEN 200 AND 299)      AS successes
FROM requests
WHERE timestamp >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;

This is the canonical countIf pattern for observability dashboards.

Percentage calculation

SELECT
    region,
    round(countIf(converted = 1) / count() * 100, 2) AS conversion_pct
FROM sessions
GROUP BY region;

Combine countIf with `count()` to compute conversion rates, error rates, or any ratio without subqueries.

Using countIf with complex predicates

SELECT countIf(price > 0 AND in_stock = 1 AND category = 'electronics') AS available
FROM products;

Any expression that returns UInt8 works as the condition, including AND/OR/NOT, BETWEEN, IN (...), and subquery-based predicates.

As a window function

SELECT
    user_id,
    event_time,
    countIf(event_type = 'error') OVER (
        PARTITION BY user_id
        ORDER BY event_time
        ROWS BETWEEN 100 PRECEDING AND CURRENT ROW
    ) AS recent_errors
FROM events;

countIf works as a window aggregate, counting errors in a sliding window per user.

Common Issues and Gotchas

• NULL conditions are treated as false. A row where the predicate evaluates to NULL (for example, comparing against a Nullable column whose value is NULL) is not counted.
• The condition must be UInt8. Passing a non-boolean column directly fails type checks. Use col != 0, col IS NOT NULL, or explicit comparison operators.
• Empty groups return 0, not NULL. If you need NULL for empty groups (for instance, to distinguish "no data" from "no matches"), stack the -OrNull combinator: countOrNullIf(cond). The -OrNull combinator must precede -If in the name.
• countIf ignores DISTINCT. countIf is not the same as countDistinctIf. To count distinct values matching a condition use `uniqIf(value, cond)` for an approximate count or uniqExactIf for an exact one.

Performance Notes

countIf is one of the fastest aggregates in ClickHouse. The compiled accumulator increments a single UInt64 counter per matching row with no intermediate allocation. Compared to sum(if(cond, 1, 0)), it skips the materialization of an Int8 intermediate column, which on large tables saves both CPU and memory. For predicates over indexed columns, prefer a WHERE clause when the same condition applies to the entire query - but for multiple conditional counts in one query, the -If combinator is strictly better.

Frequently Asked Questions

Q: How is countIf different from sum(if(condition, 1, 0))?
A: Both produce the same result, but countIf runs as a dedicated accumulator and skips the intermediate Int8 materialization. On a billion-row table, countIf typically uses less memory and finishes faster. The expression form is only equivalent when there is exactly one condition per query.

Q: Does countIf handle NULL values?
A: countIf ignores NULL conditions (treats them as false). To count rows where a column is NULL, use an explicit predicate: countIf(col IS NULL).

Q: Can I use countIf as a window function?
A: Yes. countIf(cond) OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) counts matching rows within the frame. The condition is evaluated per row, just like in a regular aggregate.

Q: How do I count distinct values matching a condition?
A: Use the -If combinator on a distinct-count aggregate: `uniqIf(value, cond)` for an approximate count or uniqExactIf(value, cond) for an exact count. countIf does not deduplicate.

Q: Can I combine countIf with other -If aggregates in one query?
A: Yes, and it is the recommended pattern. Multiple *If aggregates in one SELECT share a single pass over the data, which is far faster than separate subqueries. See avgIf, sumIf, and maxIf for siblings.

Q: What does countIf return on an empty group?
A: 0 (UInt64). For NULL instead, use the stacked combinator countOrNullIf(cond). Note the order: -OrNull comes before -If in the function name.

Pulse Surfaces countIf-Heavy Dashboards That Slow Down

Multi-condition countIf queries are the backbone of ClickHouse observability dashboards. When they slow down, the cause is usually cluster-side: hot replicas, lagging merges, memory pressure, or skewed shards. Pulse provides AI-powered monitoring for ClickHouse with automated root-cause analysis, surfacing the underlying condition before dashboards start timing out.

Related Reading

• ClickHouse sumIf Function: conditional sum, the sister combinator
• ClickHouse avgIf Function: conditional arithmetic mean
• ClickHouse count Function: the unconditional row count
• ClickHouse uniq Function: approximate distinct count, pairs with -If as uniqIf
• ClickHouse if Function: the ternary if used by the equivalent sum(if(...)) form
• ClickHouse multiIf Function: multi-branch conditional for combining counts across categories
• ClickHouse MergeTree: how indexed conditions interact with WHERE vs -If