Group by and Aggregation

The one idea: GROUP BY answers "one row per what?" — and that choice decides what every number in the result actually means. When two dashboards disagree, the cause is often just that they grouped the data at different levels, and both are correct answers to different questions.

⚠️ The trap: I grouped by region, so I can still SELECT the city column alongside it and the database will just show me the city. That feels right and it's wrong. A column that's neither in GROUP BY nor inside an aggregate has no single value per group — some databases error, but others (like SQLite) silently pick one arbitrary row's value, so that city is untrustworthy.

GROUP BY answers a question every business metric depends on: one row per what? Get the grouping column wrong, and a query can be arithmetically perfect while answering a completely different question than the one being asked.

The core idea

SELECT region, SUM(revenue) AS total_revenue
FROM sales
GROUP BY region

This collapses every row in sales down to one row per distinct region value, summing revenue within each group. The rule that matters most: every column in SELECT that isn't inside an aggregate function (SUM, COUNT, AVG, etc.) must appear in GROUP BY — otherwise the database has no way to know which value to show for that column when multiple rows collapse into one.

Reading the grain

The "grain" of a grouped query is whatever's in the GROUP BY clause — that's the "one row per what?" answer. GROUP BY region → one row per region. GROUP BY region, month → one…