Unit 04.00: GROUP BY changes the grain of your result

Unit ID: SQL-M04-U01 - Estimated active time: 13-16 minutes Objective: state the grain of an aggregated result and predict its row count before running it.

Aggregation is a grain change

Module 1 asked what one row means in a *table*. The same question applies to a *result*:

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status;
-- completed | 988
-- pending   |  12

The input grain is one order. The output grain is one status. Two rows out of a thousand.

You can predict the row count before running: it is the number of distinct combinations of the grouped columns. Two statuses means two rows - always, regardless of table size.

Grouping by more columns makes finer grain

SELECT status,
       CAST(placed_at AS DATE) AS placed_on,
       COUNT(*)                AS orders
FROM orders
GROUP BY status, placed_on
ORDER BY placed_on, status;

Now one row = one (status, day) pair. The result grows to roughly 30 days × 2 statuses. Each extra grouping column multiplies the output.

Why naming the new grain matters

A stakeholder asks "how many orders?" and you hand them the grouped result. Which number do they read? There is no single number any more - you changed the question when you grouped.

Always be able to finish this sentence about your result: *"one row is one …"*. For the first query it is "one status". If you cannot finish it, you do not yet know what you have computed.

Practice

Without running it, how many rows will this return, and what is one row?

SELECT country, status, COUNT(*)
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY country, status;

Takeaway

GROUP BY redefines what one row means. Say the new grain out loud before you read any number off the result.

---

Mark complete