When SUM() Lies: A ClickHouse WHERE Clause Skews Aggregate Counts
Aggregate functions that rely on WHERE clauses for business logic break silently when the same query runs under different filter contexts. Embedding the condition inside the aggregate keeps the metric stable regardless of how the surrounding query is composed.
A service dashboard showed one slow-call count on the summary page and a different total after drilling into the filtered list. The stats API and the list API shared a dynamic WHERE builder, but the stats endpoint sometimes ran without a state filter while the list endpoint always applied one. That mismatch meant `sum(isSlow)` aggregated either the full dataset or only rows where `isError = 0`, producing two conflicting numbers for the same metric.
The fix moved the business rule inside the aggregate function itself. Replacing `sum(isSlow)` with `sum(CASE WHEN isSlow > 0 AND isError = 0 THEN 1 ELSE 0 END)` — or ClickHouse's terser `sumIf(isSlow, isError = 0)` — decoupled the stat from whatever WHERE clause happened to be active. The list filtering stayed intact, and both views returned the same figure.
The bug is a classic scope-leak problem: the business rule 'a slow call must not also be an error' lived in the WHERE clause, but the stats query sometimes skipped that clause entirely.
ClickHouse's conditional aggregate functions are not just syntactic sugar — they prevent coupling between filtering logic and metric definitions, which matters in any codebase where a single query builder serves multiple views.
The fix is one line of SQL, but the real work was ruling out two tempting quick-fixes that would have broken list filtering or mixed error and slow-call data.