Filter Rows Before They Become Groups

The electronics summary currently combines a two-and-a-half-day projector with a twenty-and-a-half-day speaker. Suppose this report is only about items whose usual loan period is fourteen days or less. The speaker must be removed before its category totals are calculated.

Open summary.sql. Insert this line between FROM items and GROUP BY category:

WHERE loan_days <= 14

Leave every selected column, aggregate, group key, and sort clause unchanged, then press Run:

Columns: category | item_count | total_loan_days | shortest_loan_days | longest_loan_days | average_loan_days
electronics | 1 | 2.5 | 2.5 | 2.5 | 2.5
events | 2 | 21 | 7 | 14 | 10.5
tools | 2 | 10 | 3 | 7 | 5.0

The electronics group still exists, but it now has one contributing row. Its count, total, minimum, maximum, and average all come from the projector alone. The over-limit speaker never enters the group.

Follow those two source rows through the decision:

By the time grouping begins, SQLite is working with only the projector row for electronics.

WHERE works on source rows. SQLite first compares each item’s loan_days with 14 and keeps the qualifying rows. Only then does GROUP BY category collect those survivors and calculate the aggregates inside each group.

The boundary is inclusive. The projector screen has exactly fourteen days, so <= 14 keeps it. Writing < 14 would change the events summary by removing a row the brief allows.

This order of work answers a different question from calculating full category summaries and hiding some values afterward. Here the report asks, “Among items of fourteen days or less, what does each category look like?” Every number must be based on that smaller source set.

The category itself is not being filtered. Electronics stays in the output because one of its rows qualifies. A category would disappear at this stage only when none of its source rows survives the WHERE comparison, leaving nothing to group.

Submit includes an over-limit item whose removal changes its group’s count, total, maximum, and average. It also includes a fourteen-day boundary row. If only one number changes, check that the WHERE clause is inside the SQL before GROUP BY, not a calculation added to one result expression.

You already used WHERE to choose individual search rows. The same clause keeps that job here. What is new is seeing its decision flow into every later group summary.