The condition inside the aggregate: countIf and sumIf

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.

Why this matters

Five separate queries and one query with five aggregates 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 NULL, 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.