GROUP BY Explained

GROUP BY collapses many rows into fewer rows, one per distinct value (or combination of values) in the columns you group by, and lets you compute an aggregate — a count, sum, average — across each group instead of across the whole table. The single most useful question to ask about any GROUP BY query, before reading any further, is: one output row per what? Everything else follows from answering that correctly.

Aggregate functions, and the COUNT(*) vs. COUNT(column) split

COUNT, SUM, AVG, MIN, MAX are the standard aggregates, and every one of them except COUNT(*) quietly skips NULL values. That's not a minor detail — COUNT(*) and COUNT(some_column) can give genuinely different answers on the identical rows. Verified against a real join where three users produce four joined rows (one user has two orders, one has one, one has none — so that last user's order columns come back NULL):

SELECT count(*) AS all_rows, count(o.id) AS non_null_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- all_rows  non_null_orders
-- 4         3

count(*) counted every row the join produced, including the one with no real order attached. count(o.id) counted only the rows where o.id actually had a value. After a LEFT JOIN specifically, this distinction is the difference between "how many rows exist" and "how many actually have an order" — using the wrong one silently over- or under-counts by exactly the number of unmatched rows.

GROUP BY with more than one column changes the grain

GROUP BY customer_id means one row per customer. Add a second column — GROUP BY customer_id, order_date — and it becomes one row per customer per day, even for a customer who ordered on ten different days; that customer now gets ten rows, not one. Nothing about the syntax warns you this happened — the query still runs, the result just answers a narrower question than "one row per customer" might suggest at a glance. Every column added to GROUP BY is a decision about what one output row is allowed to represent.

HAVING filters groups; WHERE filters rows

This is the one genuinely confusing part, and it comes straight from execution order: WHERE runs before grouping happens, on individual rows, so it has no access to an aggregate value that doesn't exist yet. HAVING runs after grouping, against the already-computed aggregate, which is the only reason this is legal at all:

SELECT u.id, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id
HAVING count(o.id) > 0;

-- id  order_count
-- 1   2
-- 2   1

Verified: user 3 (Carol, zero orders) is excluded by HAVING count(o.id) > 0 — a condition WHERE genuinely cannot express, because at the point WHERE runs, no counting has happened yet. Trying to write WHERE count(o.id) > 0 instead fails outright in every major database, which is a useful error in practice: it's telling you the aggregate doesn't exist at that point in execution, not that the condition is wrong.

Put another way: filter individual rows before they're grouped with WHERE ("only orders from this year"); filter entire groups after aggregation with HAVING ("only customers with more than 5 orders"). Both can appear in the same query, doing different jobs at different points in SQL's actual execution order.

The rule about what's allowed in SELECT — and why it isn't the same everywhere

The standard rule: every column in SELECT that isn't wrapped in an aggregate function must appear in GROUP BY. The reasoning is mechanical — once rows are collapsed into one group, a plain (non-aggregated) column has no single value left to return if the rows in that group actually differed on it, so the database needs to know it's safe to pick just one value.

Databases enforce this rule to very different degrees, which is worth knowing before you write a query on one and run it on another. SQLite is permissive by default — verified: it silently accepts a SELECT column that's neither aggregated nor in GROUP BY, and picks an arbitrary row's value for it, with no error and no guarantee about which row's value you get if there's more than one candidate. PostgreSQL rejects that same query outright with a "must appear in the GROUP BY clause or be used in an aggregate function" error. MySQL used to behave like SQLite by default — until version 5.7.5, when it switched to rejecting these queries by default too, via the ONLY_FULL_GROUP_BY SQL mode (still overridable, but off by default going back nearly a decade now). If a query that groups loosely happens to work on SQLite or an old MySQL config, that's permissiveness, not correctness — it's one schema migration or database switch away from either an outright error or a silently non-deterministic result.

A LEFT JOIN before GROUP BY can quietly inflate an aggregate

Joining a "one" table to a "many" table before aggregating is a frequent, subtle mistake: if you join users to orders and then SUM a column that actually lives on users (something like a stored account balance, unrelated to individual orders), a user with three orders gets that same balance counted three times in the sum — once per joined row — because the join happens before the aggregation does, and duplicating the user row for each order also duplicates whatever came from the user side of the join. The fix is either aggregating the orders side into one row per user before joining it back to users, or using a subquery/CTE that computes the per-user aggregate independently rather than aggregating across an already row-multiplied join.

GROUP BY vs. DISTINCT

When there's no aggregate function involved at all, SELECT DISTINCT col FROM t and SELECT col FROM t GROUP BY col return the same rows — both collapse to one row per distinct value. GROUP BY is the more general tool because it's required the moment an aggregate enters the picture (DISTINCT alone can't compute a COUNT or SUM per group); reach for it as soon as the question becomes "one row per X, plus some number about that group" rather than just "the distinct values of X."

Try it yourself

Paste a query with a GROUP BY you're unsure about into SQL Formatter to read it more easily, or SQL Explain Helper to see how expensive the grouping actually is against real data once it runs. Both run entirely in your browser.