Aggregations & GROUP BY
Collapsing many rows into one summary row per group — counts, sums, and averages — plus the HAVING clause that filters groups.
Try answering in your head first, then click a question to check the model answer.
Q1.Why does `SELECT department, COUNT(*) FROM employees GROUP BY department` work, but adding a non-grouped, non-aggregated column would fail (or be undefined) in most databases?(show answer)
Each output row represents one group (one department), so any selected column must have a single well-defined value per group — either it's the grouping column itself, or it's reduced to one value via an aggregate function (COUNT, SUM, etc.). A raw, non-aggregated column from a multi-row group has no single defined value to display.
Q2.What's the difference between `COUNT(*)` and `COUNT(some_column)`, and why does it matter?(show answer)
COUNT(*) counts every row in the group regardless of NULLs. COUNT(some_column) counts only rows where that specific column is non-NULL. This matters when you want "how many rows have this field filled in" versus "how many rows total" — using the wrong one silently gives a misleading count when NULLs are present.
Q3.How would you find, per customer, their total spend, but only show customers whose total exceeds $1000?(show answer)
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000; — group orders by customer, aggregate with SUM, then filter on the aggregated value using HAVING (not WHERE, since the filter depends on the aggregate result).
Q4.Why might `GROUP BY` a high-cardinality column (like a UUID) perform poorly on a very large table?(show answer)
The database typically needs to either sort the rows by the group key or build an in-memory hash table of groups to aggregate them — with a high-cardinality column, that means effectively as many groups as rows, so the aggregation work approaches the cost of a full sort/hash of the entire table, with little reduction in output size to show for it.