Build One Summary per Category

One catalog-wide average mixes electronics, events equipment, and tools together. The lending desk would learn more from three smaller summaries, one for each category, so it can compare groups that serve different needs.

Open summary.sql. Add category as the first selected column, then place these two clauses after FROM items:

GROUP BY category
ORDER BY category ASC;

Keep all five aggregate expressions. The complete shape is category first, followed by count, total, shortest, longest, and average.

Press Run. SQLite returns one row per category:

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

GROUP BY category collects source rows that share the same category value. Each aggregate then works inside one group instead of across the whole table. The electronics count is 2 because two rows contribute to that group, and its total 23.0 comes only from their two periods.

Trace the events rows as a concrete group. The folding table contributes 7 days and the projector screen contributes 14. Their group has a count of 2, a total of 21, endpoints 7 and 14, and an average of 10.5. Tool and electronics values never enter those calculations.

The selected category value is called the group key. It tells the reader which group each summary row describes. Without it, three rows of numbers would arrive with no label connecting them to electronics, events, or tools.

Every source item has one category value, so each row contributes to one group here. A new category would create a new result row automatically; you do not write one query per category or list the known category names in advance.

ORDER BY category ASC makes the group rows predictable. Grouping decides which rows belong together; it does not promise the order in which groups appear. The final clause asks for their category names in ascending order.

Submit varies category names, group sizes, insertion order, and positive fractional periods. Make sure every aggregate remains in the query and is calculated inside its category. Selecting an individual item name would not make sense here because a group can contain several different names.

If every row still describes one item, check for GROUP BY category. If you get one summary for the whole table, make sure category is both selected and used as the group key.

The report has changed shape again. It no longer says only “Here is the catalog summary.” It says “Here is one complete summary for each kind of item.”