GROUP BY is the SQL clause that partitions the rows of a query result into groups that share identical values in one or more expressions, so that aggregate functions such as COUNT, SUM, AVG, MIN and MAX return one row per group instead of one row for the whole table. In SQL and relational databases, it is the standard way to turn detailed transaction rows into summaries such as totals per customer, orders per day or average response time per server.
The clause sits between WHERE and HAVING in the logical order of a SELECT statement. Rows are first filtered by WHERE, then grouped, then the groups are filtered by HAVING, and only then are the output columns computed and sorted. Understanding this order explains most errors and surprises that developers meet when they write grouped queries.
How GROUP BY evaluates a query
GROUP BY works by forming one bucket per distinct combination of grouping values and then running every aggregate function over the rows in each bucket. The output has exactly as many rows as there are distinct grouping keys that survive the WHERE filter, and every output column must be either a grouping expression or an aggregate.
The table below shows the logical processing order of a grouped SELECT statement. Physical execution may differ, because the optimizer can reorder steps, but the result must always be the same as if this order were followed.
| Step | Clause | What happens to the rows |
|---|---|---|
| 1 | FROM and JOIN | Source tables are combined into one row set |
| 2 | WHERE | Individual rows are filtered before grouping |
| 3 | GROUP BY | Remaining rows are partitioned by the grouping key |
| 4 | HAVING | Whole groups are kept or discarded by aggregate conditions |
| 5 | SELECT | One output row per group is computed |
| 6 | ORDER BY and LIMIT | Group rows are sorted and trimmed |
Because grouping happens before the SELECT list is evaluated, a column alias defined in SELECT is not visible to WHERE or GROUP BY in standard SQL. Some engines, including PostgreSQL and MySQL, relax this rule for GROUP BY, but portable queries repeat the expression or move it into a subquery or CTE.
NULL values form a single group. Although NULL is never equal to NULL in a comparison, the SQL standard treats all NULLs in a grouping column as one group, so a query grouped by region returns one row for rows with no region at all.
Aggregate functions and grouping variants
Every grouped query depends on aggregate functions, which reduce many input values to one value per group. Most aggregates ignore NULL inputs, which is why COUNT(*) and COUNT(column) can return different numbers for the same group.
| Function | Returns per group | NULL handling |
|---|---|---|
| COUNT(*) | Number of rows | Counts rows with NULLs |
| COUNT(column) | Number of non-NULL values | Skips NULLs |
| COUNT(DISTINCT column) | Number of distinct non-NULL values | Skips NULLs |
| SUM and AVG | Total and arithmetic mean | Skip NULLs; NULL if all inputs are NULL |
| MIN and MAX | Smallest and largest value | Skip NULLs |
| STRING_AGG or GROUP_CONCAT | Concatenated text | Skip NULLs; syntax varies by engine |
Beyond the plain clause, the SQL standard defines grouping extensions that compute several levels of summary in one pass. They replace queries that would otherwise combine several grouped SELECT statements with UNION ALL.
- ROLLUP(a, b) produces groups for (a, b), (a) and a grand total, which suits hierarchical subtotals such as year and month.
- CUBE(a, b) produces every combination of the listed columns, including (b) alone, which suits cross-tab reports.
- GROUPING SETS lists the exact combinations required, which gives the most control.
- The GROUPING() function returns 1 when a NULL in the output marks a subtotal row rather than a real NULL value.
GROUP BY compared with HAVING, DISTINCT and window functions
GROUP BY is one of several SQL tools that deal with repeated values, and choosing the wrong one changes the shape of the result. The key distinction is whether the query should collapse rows into summaries or keep every row and add computed context to it.
| Feature | Effect on row count | Typical purpose |
|---|---|---|
| GROUP BY | One row per group | Totals, counts and averages per key |
| HAVING | Removes whole groups | Filter on aggregate results such as SUM above a limit |
| SELECT DISTINCT | One row per distinct combination | Remove duplicates without aggregates |
| Window function with PARTITION BY | Keeps every row | Running totals, rankings and shares within a group |
| Correlated subquery | Keeps every row | Per-row lookup of a group value; often slower |
A query grouped by every selected column with no aggregates returns the same rows as SELECT DISTINCT, and many optimizers execute both identically. The difference is intent: DISTINCT states deduplication, while GROUP BY states summarization. A window function is the right choice when a report needs both the detail row and its group total on the same line.
How database engines execute grouping
Engines implement GROUP BY with two main physical strategies: sorting and hashing. The query planner chooses between them from estimated row counts, the number of distinct groups and available memory, and the choice often dominates the run time of reporting queries.
| Strategy | How it works | Best when |
|---|---|---|
| Sort-based grouping | Sorts rows by the key, then aggregates adjacent equal values | An index already delivers rows in key order |
| Hash aggregation | Builds an in-memory hash table keyed by group | Groups are few relative to rows and fit in memory |
| Streaming over an index | Reads a B-tree index in order with no separate sort | The grouping columns lead a suitable index |
| Parallel partial aggregation | Workers aggregate subsets, then results are combined | Large tables on multi-core servers |
When a hash table outgrows its memory budget, controlled in PostgreSQL by work_mem, the engine spills to disk and slows sharply. A B-tree index whose leading columns match the grouping key lets the planner skip both the sort and the hash table, which is one of the most effective optimizations for frequent summary queries.
Common mistakes with GROUP BY
Most GROUP BY defects come from columns that are neither grouped nor aggregated and from filters placed in the wrong clause. The following patterns appear repeatedly in production code reviews.
- Selecting an ungrouped column. Standard SQL rejects it; older MySQL modes without ONLY_FULL_GROUP_BY returned an arbitrary value from the group, which silently produces wrong reports.
- Filtering aggregates in WHERE. A condition on SUM or COUNT belongs in HAVING, while row conditions belong in WHERE, where they also reduce the work.
- Grouping after a fan-out join. Joining orders to order lines before summing order totals multiplies values; aggregate first, then join.
- Assuming sorted output. GROUP BY does not guarantee order; add ORDER BY explicitly whenever order matters.
- Grouping by a high-precision timestamp. Each distinct millisecond becomes a group; truncate to the hour or day first.
The short version
Imagine a pile of shop receipts. GROUP BY is like sorting the receipts into separate stacks, one stack per customer, and then writing a single summary card for each stack: how many receipts it holds and how much money they add up to.
The database does the sorting and adding for you. You choose what defines a stack, such as customer, day or product, and you choose what to calculate for each stack. The result is a short list of summaries instead of thousands of individual rows.
Where it makes a difference
An online store keeps every sale in an orders table that grows by about 40,000 rows a day. The finance team wants daily revenue per country for the last 90 days. An analyst writes a query that filters the last 90 days in WHERE, groups by the order date truncated to the day and the country code, and returns SUM of the order amount and COUNT of orders for each group.
The first version also joined the order lines table, and revenue came out roughly three times too high because each order was counted once per line. The analyst moved the sum into a CTE that aggregates at order level before any join, and added HAVING to hide countries with fewer than 10 orders a day. With a composite index on order date and country, the planner streamed the rows in key order, and the report of about 5,000 rows returned in under a second instead of scanning the whole table.