Aggregate Functions
sumIf
Conditional aggregation with the -If combinator: sumIf, countIf, avgIf, quantileIf.
Signature
sumIf(column, condition)Returns
Same as the base function (sumIf follows sum; countIf is UInt64)
In TypeScript
string (Int64/UInt64 sums and countIf)
Updated · Official reference: sumIf in the ClickHouse docs
What it does
Sums only the rows where the condition is true. -If is a combinator, so countIf, avgIf, uniqIf and quantileIf work the same way.
An -If aggregate takes one extra argument, a condition, and ignores rows where it is false. That gives several conditional totals from one scan, such as paid and refunded revenue in the same SELECT. For most functions the condition goes after the column. For parametric functions such as quantile, the level stays in the first brackets: quantileIf(0.95)(response_ms, region = 'eu'). countIf takes only the condition.
Notes
- sumIf on an Array column fails with "Illegal type Array(...) of argument for aggregate function sum". Use sumArrayIf(col, cond) or sumIf(arraySum(col), cond).
- Combinators stack: sumArrayIf applies -Array and then -If.
- When no row matches, sumIf and countIf return 0.
- countIf(cond) takes only the condition; the other -If functions take the column first.
Example SQL
sumIf in ClickHouse SQL
Common questions
sumIf questions, answered
How do I fix "Illegal type Array(Int64) of argument for aggregate function sum" in sumIf?
sumIf cannot add up an Array column. Use sumArrayIf(col, cond) to add every element of the matching rows, or sumIf(arraySum(col), cond).
How does the -If combinator work in ClickHouse?
Add If to any aggregate function name and pass a condition as an extra argument: sumIf(x, cond), avgIf(x, cond), uniqIf(x, cond), quantileIf(0.9)(x, cond). Rows where the condition is false are ignored.
What does sumIf return when no rows match?
0. countIf also returns 0 when no rows match.
Is countIf a number or a string in TypeScript?
A string. countIf returns UInt64, which hypequery asks ClickHouse to send as a JSON string so large counts keep their precision.
TypeScript with hypequery
Use sumIf in a typed TypeScript query
hypequery gives you a type-safe query builder for ClickHouse. The generated schema maps your ClickHouse columns to TypeScript types, and raw SQL expressions let you incorporate functions like sumIf when you need them inside a builder query.
Python with hypequery
Use sumIf from Python
The hypequery Python SDK defines datasets of dimensions and measures and compiles queries on them to ClickHouse SQL. This example was run against ClickHouse 26.6 on the same tables as the SQL example above.
Each measure filter becomes the -If combinator in the compiled SQL: sumIf, countIf(order_id, ...) and quantileIf(0.95)(...).
Use cases
What teams use sumIf for
Several conditional totals in one query
Put one sumIf or countIf per status in the same SELECT to get paid, refunded and pending totals from a single scan.
Conditional percentiles in ClickHouse
quantileIf(0.95)(latency_ms, region = 'eu') gives the p95 for one segment. The level goes in the first brackets, the condition after the column.
Examples
Runnable examples, tested in CI
Every example runs against the same seeded ClickHouse dataset and is smoke-tested on every change. Scaffold one with npx @hypequery/cli create <slug> -y, or browse the full list.
Related guides
Next step
Use sumIf in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use sumIf alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.