Querying
GROUP BY and Grouping
GROUP BY is a clause in the SELECT statement that partitions the rows produced by the FROM and WHERE phases into groups.
Overview
GROUP BY is a clause in the SELECT statement that partitions the rows produced by the FROM and WHERE phases into groups. Each group is a set of rows that share identical values in the grouping expressions. Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX are then evaluated once per group rather than once per row, producing one output row per group. The clause has been part of the SQL standard since SQL-86 and is supported by essentially every relational database. Grouping is the conceptual operation behind it: rows are collapsed along the grouping keys, and non-aggregated columns in the select list must be functionally dependent on those keys or themselves appear in the GROUP BY clause. GROUP BY is distinct from ORDER BY, which sorts the final result, and from DISTINCT, which removes duplicate rows without computing aggregates. It is also distinct from window functions, which aggregate without collapsing rows. Understanding GROUP BY is a prerequisite for the HAVING clause, which filters groups after aggregation, and for most reporting queries.
Syntax and usage
The general form is SELECT expr, agg(expr) FROM t WHERE predicate GROUP BY expr [, expr ...] [HAVING predicate] [ORDER BY ...]. Grouping expressions may be column names, expressions, or positional ordinals in some dialects. The WHERE clause filters individual rows before grouping; the HAVING clause filters groups after aggregation. Every column referenced in the SELECT list that is not inside an aggregate function must appear in the GROUP BY list, or the query is invalid under standard SQL. Most databases enforce this; MySQL historically permitted it with ONLY_FULL_GROUP_BY disabled, and SQLite is permissive, but relying on that is non-portable. Grouping by an expression, such as GROUP BY EXTRACT(YEAR FROM order_date), is standard. PostgreSQL, SQL Server, Oracle, and MySQL all accept column aliases in GROUP BY in some cases, but the standard does not require it. Multiple keys create a composite grouping, and the result contains one row for each distinct combination of key values. Grouping sets, ROLLUP, and CUBE extend the clause for subtotal reporting and are covered separately.
How it works
Logically, the query is evaluated in this order: FROM builds the working set, WHERE filters rows, GROUP BY partitions the surviving rows into groups, aggregate functions are computed per group, HAVING filters groups, SELECT projects the output columns, and ORDER BY sorts the final rows. The grouping key is compared using equality semantics; NULL values are treated as equal to one another for grouping purposes, so all rows with a NULL key form a single group. This differs from the comparison semantics of WHERE, where NULL = NULL is unknown. Implementations typically sort the input on the grouping keys or build a hash table keyed by them. A sort-based plan produces ordered groups and can sometimes satisfy an ORDER BY on the same keys for free; a hash-based plan is usually faster when the group count is moderate and no ordering is required. The output order of a GROUP BY query is not guaranteed unless an explicit ORDER BY is present. The number of output rows equals the number of distinct grouping-key combinations present in the filtered input.
Examples
Count orders per customer: SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id. Sum revenue per month: SELECT EXTRACT(YEAR FROM order_date) AS y, EXTRACT(MONTH FROM order_date) AS m, SUM(amount) AS total FROM orders GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) ORDER BY y, m. Filter groups with HAVING: SELECT customer_id, COUNT(*) AS c FROM orders GROUP BY customer_id HAVING COUNT(*) > 5. Group by multiple columns: SELECT region, product, AVG(price) FROM sales GROUP BY region, product. Grouping with NULL keys: SELECT department_id, COUNT(*) FROM employees GROUP BY department_id returns one row for the NULL department, aggregating all employees whose department_id is unknown. Combining WHERE and HAVING: SELECT customer_id, SUM(amount) FROM orders WHERE status = 'shipped' GROUP BY customer_id HAVING SUM(amount) > 1000. Note that the WHERE predicate removes rows before aggregation, while HAVING removes groups after.
Common pitfalls
Selecting a non-aggregated column that is not in GROUP BY is the most frequent error. Standard SQL rejects it; permissive engines may return an arbitrary value from one row in the group, which is non-deterministic. Another pitfall is putting an aggregate condition in WHERE instead of HAVING, for example WHERE COUNT(*) > 5, which is invalid because WHERE runs before aggregation. Conversely, filtering individual rows in HAVING is legal but usually slower and less clear than using WHERE. Assuming GROUP BY returns sorted output is unsafe; without ORDER BY the order is implementation-defined. Grouping by a nullable column merges all NULLs into one group, which may surprise users expecting NULLs to be excluded. Grouping by a computed expression can prevent index usage unless a matching expression index exists. Finally, mixing GROUP BY with window functions in the same query is valid but the two operate at different stages; window functions run after grouping and see the grouped rows, not the original rows.
Best practices
Include every non-aggregated select column in the GROUP BY list, even in engines that do not require it, so the query is portable and its meaning is explicit. Prefer filtering rows in WHERE and reserving HAVING for conditions on aggregates; this reduces the number of rows entering the grouping step. Always add an ORDER BY when the presentation order matters, since GROUP BY does not guarantee one. Use meaningful aliases for aggregate results to keep the select list readable. When grouping by expressions, consider whether a generated or expression index can support the plan. Avoid grouping by columns with high cardinality unless necessary, because many small groups increase hashing or sorting cost. For subtotal reports, prefer GROUPING SETS, ROLLUP, or CUBE over multiple UNION ALL queries. Test queries against NULL keys explicitly, and document whether NULL forms its own group. Keep the grouping key list as short as correctness allows.
Frequently asked questions
What is the difference between WHERE and HAVING? WHERE filters individual rows before grouping and cannot reference aggregate functions; HAVING filters groups after aggregation and typically contains aggregate expressions. Can I use GROUP BY without an aggregate function? Yes, syntactically, but then it behaves like DISTINCT over the grouping columns; if you only need unique rows, DISTINCT is clearer. Why does my query fail with a column not in GROUP BY? Standard SQL requires every non-aggregated select column to be functionally dependent on the grouping keys. If the column is not in GROUP BY and not aggregated, the result would be ambiguous, so the engine rejects it. Does GROUP BY sort the result? No. Some implementations happen to return sorted groups, but the standard does not guarantee order; use ORDER BY to be explicit.