GROUP BY — Group rows by one or more columns

SECTION 1

Historical Context & Motivation

Before relational databases existed, generating summary reports from stored data required writing custom procedural programs that iterated through records, maintained running totals in memory, and output results line by line. This approach was tedious, error-prone, and tightly coupled to the physical layout of the data. Edgar F. Codd's 1970 paper introducing the relational model shifted the paradigm toward declarative data manipulation, but it was the subsequent development of SQL at IBM that gave practitioners a concrete syntax for expressing aggregation and grouping without specifying how the engine should perform the computation.

1970

Codd's Relational Model

Edgar F. Codd publishes "A Relational Model of Data for Large Shared Data Banks," establishing the theoretical foundation that would eventually support set-based operations like grouping.

1974

SEQUEL at IBM

Donald Chamberlin and Raymond Boyce design SEQUEL (later SQL) for System R, introducing the GROUP BY clause as a first-class construct for partitioning result sets.

1986

SQL-86 Standard

ANSI publishes the first SQL standard (SQL-86), formally codifying GROUP BY alongside aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.

1999

SQL:1999 — ROLLUP & CUBE

The SQL:1999 standard extends GROUP BY with ROLLUP, CUBE, and GROUPING SETS, enabling multi-level aggregation within a single query.

2003+

Window Functions & Modern Engines

SQL:2003 introduces window functions (OVER / PARTITION BY), complementing GROUP BY by allowing row-level detail alongside aggregates. Modern engines like PostgreSQL, MySQL 8, and BigQuery optimize grouping with hash-based and sort-based strategies.

The central question GROUP BY answers is deceptively simple: how do we collapse many rows into a smaller set of summary rows, where each summary represents a distinct combination of column values? Without this clause, a query containing an aggregate function like SUM() would collapse the entire table into a single row, which is rarely the insight analysts or applications need.

SECTION 2

Core Principles & Definitions

Understanding GROUP BY requires internalizing several foundational ideas that govern how the SQL engine partitions, aggregates, and filters grouped data. These principles also dictate what may legally appear in the SELECT list—a frequent source of errors for newcomers.

Partitioning

GROUP BY divides the result set into partitions (groups) based on unique combinations of the specified column values. Rows sharing identical values in all grouping columns land in the same partition.

Aggregate Functions

Functions like COUNT(), SUM(), AVG(), MIN(), and MAX() each consume the set of values within a partition and return a single scalar result.

SELECT-List Rule

Every non-aggregated column in the SELECT clause must appear in the GROUP BY clause. The engine cannot decide which of many values to display for a column that varies within a group.

HAVING vs. WHERE

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. Placing conditions in the correct clause affects both correctness and performance.

Logical Evaluation Order

SQL's logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Understanding this pipeline clarifies why aliases defined in SELECT cannot be used in WHERE or GROUP BY in standard SQL.

✦ KEY TAKEAWAY

Think of GROUP BY like sorting a deck of playing cards by suit and then counting how many cards are in each pile. The suit is your grouping column, each pile is a partition, and the count is your aggregate function. You cannot report an individual card's rank as a column in the result because each pile contains many ranks—unless you aggregate them (e.g., the maximum rank in each suit).

SECTION 3

Visual Explanation — How GROUP BY Transforms Data

The diagram shows how six detail rows from an orders table are partitioned into three groups by the dept column. Within each group, SUM(amount) reduces the member rows to a single aggregate value, producing a final result set of three rows.

Notice the key structural transformation: six input rows become three output rows. The cardinality of the result equals the number of distinct grouping-key combinations present in the data after WHERE filtering. Each group is an independent aggregate context—the SUM for Sales knows nothing about the rows in the Eng group. This isolation is what makes GROUP BY both powerful and predictable: the aggregate function operates on exactly the rows in its partition, never more and never fewer.

SECTION 4

How GROUP BY Works — The SQL Logical Pipeline

SQL defines a logical evaluation order that determines when each clause takes effect. Although query optimizers are free to rearrange operations physically, the logical order governs semantic correctness. Understanding this pipeline is essential for writing correct GROUP BY queries and debugging unexpected results.

SQL LOGICAL EVALUATION ORDER

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

Each arrow represents a transformation of the intermediate virtual table. GROUP BY collapses the row set into groups; HAVING filters those groups; SELECT then extracts the final columns and computes expressions.

The SELECT-List Constraint

Once GROUP BY has executed, every row in the intermediate result represents a group, not an individual record. Consequently, the SELECT clause may reference only grouping columns and aggregate expressions. A column like order_id that varies within a group is ambiguous—the engine cannot choose which of the many values within the group to return. Standard SQL (and strict-mode MySQL, PostgreSQL) will raise an error. Older MySQL versions in permissive mode would silently pick an arbitrary value, a behavior widely considered a design mistake that has been tightened in recent releases.

VALID SELECT RULE

∀ col ∈ SELECT_list : col ∈ GROUP_BY_columns ∨ col = agg_fn(expr)

For every column col in the SELECT list, it must either appear in the GROUP BY clause or be wrapped in an aggregate function. This is sometimes called the single-value rule.

Multi-Column Grouping

When you group by more than one column—GROUP BY dept, region—the engine creates a partition for each distinct tuple of (dept, region). If there are 3 departments and 4 regions, there could be up to 12 groups (the Cartesian product), though typically fewer groups exist because not every combination appears in the data. The number of output rows equals the number of distinct tuples, which is bounded by |D₁| × |D₂| × … × |Dₖ| where |Dᵢ| is the domain size of the iᵗʰ grouping column.

MAXIMUM OUTPUT CARDINALITY

|result| ≤ min(N, |D₁| × |D₂| × … × |Dₖ|)

N = number of input rows after WHERE filtering, Dᵢ = distinct values of the iᵗʰ grouping column. The result can never exceed the input row count, and it is further bounded by the cross-product of column domains.

SECTION 5

Aggregate Functions — The Companions of GROUP BY

GROUP BY is almost always paired with one or more aggregate functions. While GROUP BY defines the groups, the aggregates define what computation to perform within each group. The SQL standard specifies five core aggregates, plus additional functions that many engines support.

The five core SQL aggregate functions are shown with example inputs and outputs. The lower panel summarizes critical NULL-handling semantics and the DISTINCT modifier, both of which are frequent sources of subtle bugs.

Core SQL aggregate functions and their NULL-handling behavior

Function	Input Type	Return Type	NULL Behavior
`COUNT(*)`	Any	Integer	Counts all rows, including NULLs
`COUNT(col)`	Any	Integer	Skips NULL values
`SUM(col)`	Numeric	Numeric	Ignores NULLs; returns NULL if all NULL
`AVG(col)`	Numeric	Numeric / Decimal	Ignores NULLs (denominator excludes them)
`MIN(col)`	Orderable	Same as input	Ignores NULLs
`MAX(col)`	Orderable	Same as input	Ignores NULLs

SECTION 6

Worked Example — Sales Report by Region and Category

Consider a sales table with columns sale_id, region, category, amount, and sale_date. We want to produce a report showing total revenue and order count for each region-category pair, but only for groups with more than two orders, sorted by total revenue descending.

Multi-Column GROUP BY with HAVING

Step 1 — Identify the Grouping ColumnsThe report requests summaries "for each region-category pair," so we need GROUP BY region, category. Each unique (region, category) tuple becomes one output row.

Step 2 — Choose Aggregate Functions"Total revenue" maps to SUM(amount) and "order count" maps to COUNT(*). Both are applied within each group independently.

Step 3 — Apply the Group Filter (HAVING)"Only for groups with more than two orders" is a condition on an aggregate, so it belongs in the HAVING clause, not WHERE. We write HAVING COUNT(*) > 2. Placing this in WHERE would cause a syntax error because WHERE executes before grouping.

Step 4 — Sort the Results"Sorted by total revenue descending" translates to ORDER BY total_revenue DESC. In standard SQL, we can reference the alias defined in SELECT, or repeat the expression ORDER BY SUM(amount) DESC.

Step 5 — Assemble the Complete QueryCombining all parts yields the final query:

SELECT region, category, SUM(amount) AS total_revenue, COUNT(*) AS order_count FROM sales GROUP BY region, category HAVING COUNT(*) > 2 ORDER BY total_revenue DESC;

Step 6 — Trace the ExecutionSuppose the sales table has 10 rows with regions {East, West} and categories {A, B, C}. FROM reads all 10 rows. No WHERE clause filters them. GROUP BY partitions into up to 6 groups (2 × 3). Suppose (East, A) has 4 rows, (West, B) has 3, and all others have ≤ 2. HAVING eliminates the small groups, leaving 2 output rows. SELECT computes the aggregates for these two groups, and ORDER BY sorts them.

Final result: 2 rows showing (East, A) and (West, B) with their totals and counts, ordered by total_revenue descending.

SECTION 7

Common Pitfalls, Strengths, and Best Practices

Common GROUP BY pitfalls and recommended best practices

Pitfall / Issue	Why It Happens	Best Practice
Non-aggregated column in SELECT	Column not in GROUP BY but appears in SELECT without an aggregate wrapper. Standard SQL raises an error; permissive engines return nondeterministic values.	Always include every non-aggregated SELECT column in GROUP BY, or wrap it in an aggregate like MAX(col).
Filtering on aggregates with WHERE	WHERE is evaluated before GROUP BY, so aggregate functions are not yet computed.	Use HAVING for conditions on aggregates. Reserve WHERE for row-level predicates to enable early filtering and index usage.
COUNT(*) vs. COUNT(col) confusion	COUNT(*) counts all rows; COUNT(col) excludes NULLs. The difference is invisible when no NULLs exist but can cause subtle bugs otherwise.	Be explicit: use COUNT(*) when you want row counts, COUNT(col) only when NULL exclusion is intentional.
Grouping on high-cardinality columns	Grouping by a near-unique column (e.g., a timestamp at millisecond precision) produces groups of size 1, making aggregation pointless.	Reduce cardinality with expressions: DATE_TRUNC('month', ts), or bucket numeric values with FLOOR(val/10)*10.
Performance degradation on large tables	GROUP BY may trigger sort-based or hash-based aggregation. Without appropriate indexes or with excessive grouping columns, memory and CPU usage spike.	Filter early with WHERE to reduce the grouping input. Consider composite indexes on (grouping_cols, aggregated_cols). Use EXPLAIN to inspect the plan.

✦ KEY TAKEAWAY

GROUP BY is one of the most powerful tools in SQL, but its power comes with a strict contract: every column you surface in the result must either define the group or summarize it. Think of it like a pivot table in a spreadsheet—the row labels are your GROUP BY columns, and the values area contains your aggregates. If you try to put a detail-level field into the values area without aggregation, the spreadsheet doesn't know which row's value to display, and neither does the database.

SECTION 8

Connection to Advanced Grouping — ROLLUP, CUBE, and Window Functions

The basic GROUP BY clause produces a flat set of groups at a single level of granularity. Real-world reporting, however, often requires hierarchical subtotals, cross-tabulations, or the ability to retain individual row detail alongside group-level aggregates. SQL has evolved to meet these needs through extensions to GROUP BY and the introduction of window functions.

Advanced grouping features and their relationship to basic GROUP BY

Feature	What It Does	Relationship to GROUP BY
`GROUP BY ROLLUP(a, b)`	Produces groups at (a, b), (a), and grand total levels—a hierarchy of progressively coarser aggregations.	Extends GROUP BY by automatically adding subtotal and grand total rows with NULL placeholders for rolled-up columns.
`GROUP BY CUBE(a, b)`	Produces groups for every subset of {a, b}: (a, b), (a), (b), and the grand total. Useful for OLAP-style cross-tabulation.	A superset of ROLLUP. The number of grouping sets is 2ᵏ where k is the number of columns.
`GROUPING SETS((a), (b))`	Allows explicit enumeration of desired grouping combinations, giving fine-grained control over which aggregation levels appear.	The most flexible form; ROLLUP and CUBE are syntactic sugar for common GROUPING SETS patterns.
`SUM(x) OVER (PARTITION BY a)`	Computes an aggregate across a partition without collapsing rows. Each input row is preserved and annotated with the partition aggregate.	Complementary to GROUP BY: window functions add aggregate context without reducing cardinality. Introduced in SQL:2003.

As you advance in SQL proficiency, you will find that window functions (OVER / PARTITION BY) solve many problems that developers previously addressed with self-joins or subqueries containing GROUP BY. The key distinction is that GROUP BY reduces the row count (many-to-one), while window functions preserve the original row count (many-to-many). Mastering both gives you a complete toolkit for analytical SQL queries.

💡 When to Use What

Use GROUP BY when you need a summary report with fewer rows than the input. Use window functions when you need to compare each row against its group aggregate (e.g., "what percentage of the department total does this row represent?"). Use ROLLUP/CUBE when your report needs subtotals at multiple hierarchy levels in a single query.

SECTION 9

Practice Problems

The following problems use a table called employees with columns: emp_id (INT), name (VARCHAR), department (VARCHAR), salary (DECIMAL), hire_date (DATE), and manager_id (INT, nullable).

PROBLEM 1 — CONCEPTUAL

Explain why the following query is invalid in standard SQL: SELECT department, name, COUNT(*) FROM employees GROUP BY department; What specific rule does it violate, and how would you fix it?

PROBLEM 2 — BASIC CALCULATION

Write a query that returns each department and the average salary within that department. Alias the average as avg_salary.

PROBLEM 3 — INTERMEDIATE

Write a query to find departments where the total salary expenditure exceeds $500,000. Return the department name and total salary, sorted by total salary descending.

PROBLEM 4 — APPLIED

An HR analyst needs a report showing the number of employees hired per department per year, but only for years after 2019 and only for department-year combinations with at least 5 hires. Write the query using appropriate date functions (you may assume PostgreSQL syntax).

PROBLEM 5 — CRITICAL THINKING

Consider the query:

SELECT department, COUNT(*) AS n, AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

(a) What does this query return? (b) Is the subquery in HAVING evaluated once or once per group? (c) Could you rewrite this without a subquery, and what trade-offs would be involved?

SUMMARY

Summary — GROUP BY at a Glance

The GROUP BY clause partitions the result set into groups based on one or more columns, enabling aggregate functions such as COUNT, SUM, AVG, MIN, and MAX to compute per-group summaries. The single-value rule dictates that every non-aggregated column in SELECT must appear in the GROUP BY list. Row-level filtering belongs in WHERE (before grouping), while group-level filtering belongs in HAVING (after grouping).

SQL's logical evaluation order (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY) is the key to reasoning about query correctness. Multi-column grouping creates partitions for each distinct tuple of grouping values. Extensions like ROLLUP, CUBE, and GROUPING SETS add hierarchical and cross-tabulated subtotals, while window functions complement GROUP BY by computing aggregates without collapsing rows. Mastering GROUP BY is foundational for analytical SQL and the gateway to advanced data summarization techniques.