GROUP BY for categorizing data

Concepts covered: Grouping

GROUP BY takes a column, reads its value on every row, and puts rows that share a value into the same group. The query then returns 1 row per group, and the only things that row can show are the grouping value itself and numbers computed over the whole group. The rows underneath do not go anywhere: the source table keeps every one of them, and the result is a second, shorter table built on top.

6 sales, 3 regions, 3 result rows

The transactions table below holds 6 sales. 3 of them are tagged North, 2 South and 1 East. A manager who wants a count per region is asking for 3 numbers, which means 3 rows, and GROUP BY region is the clause that decides those 3 rows exist.

Watch the order of events. The database reads all 6 rows first. It then sorts each row into a bucket labelled with its region value, so 3 buckets form: North holding sales 1, 3 and 5, South holding 2 and 4, East holding 6. Only after the buckets exist does COUNT(*) run, once per bucket, and that is where 3, 2 and 1 come from. Grouping is structural and happens first; the aggregate is arithmetic over whatever landed in each group.

The number of result rows equals the number of distinct values in the grouping column. A table of 10 million sales across 3 regions returns 3 rows. The same table grouped by sale_id returns 10 million rows, because every value is distinct and every group holds a single row. Knowing how many distinct values a column has tells you the shape of the result before the query runs.

The rule every grouped query obeys

Every column in the SELECT list of a grouped query is either named in GROUP BY or wrapped in an aggregate function. There is no third option. The reason is a question the database cannot answer: the North group holds 3 rows with 3 different sale_id values, 1, 3 and 5. A result row for North has room for 1 value. Which one? Standard SQL refuses to guess and raises an error instead.

SELECT region, sale_id, COUNT(*) AS sale_count
FROM transactions
GROUP BY region
-- error: sale_id must appear in the GROUP BY clause or be used in an aggregate function

MySQL in its older default mode is the famous exception: it picks one of the 3 values and returns it without complaint, which is how a report can show a sale id next to a count and be wrong on every row. Treat the rule as absolute even where the engine is lenient. Two fixes exist: add sale_id to GROUP BY, which changes the grain to 1 row per sale, or wrap it in an aggregate such as MIN(sale_id), which asks a question with 1 answer.

Grouping by 2 columns

Adding a column to GROUP BY makes the groups finer. GROUP BY region, channel creates 1 group per distinct pair of values, so North web and North store become 2 separate rows. More grouping columns means more rows and smaller groups, down to the point where each group holds 1 row and the query stops summarizing anything. Run the query below as written, then change the grouping to region, channel and add channel to the SELECT list to watch the 3 rows become 5.

SELECT region, COUNT(*) AS sale_count
FROM transactions
GROUP BY region

The 5-row result has North twice, once for web with 2 sales and once for store with 1. Every grouping column has to be in SELECT for the result to be readable, and every SELECT column that is not aggregated has to be in the grouping, so the 2 lists are usually written to match. The combination of grouping columns is called the grain of the result: 1 row per region, or 1 row per region and channel.

A report needs 1 row per region showing the region and how many sales it had. Which query runs on a standard SQL engine and returns exactly that?

• SELECT region, COUNT(*) AS sale_count FROM transactions GROUP BY region
• SELECT region, amount, COUNT(*) AS sale_count FROM transactions GROUP BY region
• SELECT region, COUNT(*) AS sale_count FROM transactions GROUP BY region, channel
• SELECT DISTINCT region FROM transactions

About this lesson

This section is part of the Aggregating: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.