Level 3 · Orders by channel and status

GROUP BY splits the rows into groups that share the same values, then runs the aggregate once per group:

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel;

That returns one row per channel. Add a second column and you get one row per combination:

SELECT channel, status, COUNT(*) AS orders
FROM orders
GROUP BY channel, status;

The one rule

Every column in the SELECT list must either be inside an aggregate function or appear in GROUP BY. This fails in Postgres, DuckDB and BigQuery:

SELECT channel, status, COUNT(*)
FROM orders
GROUP BY channel;   -- error: status is neither grouped nor aggregated

The database cannot show a single status for a group that contains many statuses. Older MySQL versions (or MySQL with ONLY_FULL_GROUP_BY turned off) silently return an arbitrary status instead of erroring, which is worse. DuckDB has a GROUP BY ALL shortcut that groups by every non-aggregated column; it is handy, but not portable to Postgres or MySQL (BigQuery and Snowflake have added it recently).

Combinations that do not exist

GROUP BY only returns groups that have at least one row. If the marketplace channel never had a pending order, there is no “marketplace, pending, 0” row. When a report needs zeros for missing combinations, you build the full grid of combinations and LEFT JOIN the counts onto it, a technique from chapter 5.

Sorting grouped results

Grouped results come back in no guaranteed order. Add ORDER BY when it matters, and you can sort by the aggregate’s alias:

SELECT channel, COUNT(*) AS orders
FROM orders
GROUP BY channel
ORDER BY orders DESC;

Grouping by expressions

You can group by any expression, as long as the SELECT list uses the same one:

SELECT EXTRACT(YEAR FROM order_date) AS order_year, COUNT(*) AS orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);

Postgres, DuckDB, MySQL and BigQuery also accept GROUP BY order_year (the alias) or GROUP BY 1 (the position). Positions are common in analytics code, but names survive edits better.

Common mistakes

• Grouping by a high-cardinality column like order_id and wondering why every count is 1.
• Including an extra column “just to see it”, which splits the groups and changes every number. If you need a descriptive column such as a product name next to its ID, group by both.