The reading room: what an answer is measured in

The condition inside the aggregate: countIf and sumIf

20 min

What you'll learn

move a condition OUT OF WHERE and into the : countIf, sumIf, avgIf, uniqIf
build a five-metric summary in one pass instead of five queries
state the price of this method with three numbers, not the word “faster”
distinguish count(condition) from countIf(condition) — they answer different questions, and the first is almost never the one you meant to ask

The curator’s second question

Same day, forty minutes later. The curator comes back with a refinement: this time, visits should not be counted as one total. They need to be split by device — reading-room terminal, tablet, or glasses.

For each device, you need five metrics: how many visits there were, how many ended with nothing, how many distinct visitors actually reached a title, how many titles were opened, and the average visit duration.

The previous shift’s log already has the answer, assembled from five separate queries. Each query calculates one metric and rereads the visits table from scratch. The numbers are correct, but the same data gets scanned again and again.

So the question is not how to get the answer. You need to understand the price of this method, and whether the same numbers can be produced in one pass.

Nothing is broken here. You are choosing between two working solutions. You’ll see situations like this more often than failures throughout the course: the archivist on desk duty spends most of a shift choosing, not fixing.

The duty desk: five sheets fanned out, one metric on each, and lying across them a single sheet where those same five columns stand together in one table; to the side an inset — five arrows running down one column of data, and one arrow running down it once. — Five separate answers to one and the same question come together on a single sheet — and the column is read once instead of five times.

Five metrics in one pass over the visits table. The condition is not in WHERE; it sits inside each aggregate. That lets one row calculate metrics for different conditions at the same time.

Why this matters

Five separate queries and one query with five can return exactly the same numbers. But the engine does different amounts of work to produce them.

In a row-oriented database, the difference is often less visible because the whole row is read anyway. In a columnar store, rereading the columns you need is much easier to see — and its price can be measured.

Each of the previous shift’s five queries reads the table again. In total, the engine reads the same 5272 rows five times. If you move the condition inside the aggregate, the rows are read once, and the engine calculates all five aggregates during that same pass.

The next cell shows the difference with the same three numbers as before: how many parts, rows, and marks each method needed.

The trap that makes reports disagree

Putting the condition inside the aggregate matters for correctness too. It is easy to write a query here that runs without error and calculates the wrong thing.

count(x) and countIf(x) are not synonyms. count(x) counts every value of x except , and the result of a boolean expression here is never NULL: both “true” and “false” are ordinary values.

So count(bounced = 1) returns exactly the same number as count() — every visit. There is no error and no suspicious warning. The query returns a perfectly plausible number, which is why this mistake is often found later, when two reports stop matching.

You have already seen conditional aggregation in the SQL course as SUM(CASE WHEN … THEN 1 END). There is only one new idea here: now we care not just about the result, but also about the price of the query form you chose.

QUERY: Five queries and one query give the same answer. The difference is how many times you sent the reading room back for the same rows. Even refusing to combine five queries into one comes at a price.

Two ways to get the same answer. Below are five separate queries stitched together; above is the same summary in one pass. Compare how much data each version had to read.

Five queries read the table five times — 26 360 rows; one query passes through it once — 5272 rows, with all five aggregates calculated during that pass.

Find and fix the bug

In the previous shift’s log, four metrics were built with subqueries. The problem is visible in the result: those four metrics repeat across all three rows. The subquery does not know which group called it, so it calculates the same thing every time. Fix the query so that each metric is calculated for its own group and the table is read only once. Keep the column names and row order unchanged.

A subquery in the select list runs separately and knows nothing about the outer query’s GROUP BY. That is why it returns the same result for every row. Move the condition inside the aggregate: countIf, uniqIf, sumIf, avgIf.

Interview question

How this comes up in interviews

A classic version is: “You have an events table and need a mart with ten metrics by segment. How would you write it?” Writing ten and joining them is treated as a mistake here, not a matter of style. The expected method is one pass with ten conditional .

Then they often check whether you understand the mechanism rather than just know the function names. The question is: “How is countIf(x > 0) different from count(x > 0)?” They expect you to explain that the second counts every value except , not just true values, so it returns the total number of rows in the group.

A third question comes up less often: “Is sum(if(cond, x, 0)) the same thing?” For numeric values, the result is the same, but sumIf expresses the condition directly and is easier to read.

Check yourself

In the summary by device a column is written as count(bounced = 1). What will it show?

Practice: solve the tasks

Solved 0 of 4 · any 3 is enough to pass

Key takeaways

question	method	price
Five metrics by device	five separate queries	5 parts / 26 360 rows / 5 marks
Same question	one pass, condition inside the	1 part / 5272 rows / 1 mark

Both methods return the same answer, but the second reads exactly one fifth as many rows. The reason is simple: five separate queries read the table rows five times, while conditional aggregates calculate several metrics in one pass.

The main idea of the lesson is broader than the functions we named. In ClickHouse, an aggregate can take a condition inside it. countIf, sumIf, avgIf, uniqIf are not four unrelated functions, but examples of one general rule.

In the next lesson, we’ll examine the general rule behind all four functions.