The GROUP BY clause partitions tabular records into distinct buckets based on matching values across specified grouping columns.
Once rows are divided into buckets, aggregate functions compute summary figures independently for each separate group.
In relational algebra, grouping transforms a fine-grained relation into an aggregated relation with one row per category.
Grouping by multiple columns creates sub-groups, such as grouping by country and then city to reveal regional patterns.
A strict syntactic rule governs grouping queries: every column in the SELECT list must either be in GROUP BY or aggregated.
Selecting a raw unaggregated column that is not in GROUP BY produces an ambiguous query that standard SQL engines reject.
Some legacy systems historically permitted unaggregated columns, returning arbitrary row values that produced corrupted reports.
Modern databases support the GROUPING SETS extension, which computes multiple levels of aggregation in a single query execution.
Related extensions include ROLLUP for hierarchical subtotals and CUBE for cross-tabulated multi-dimensional totals.
NULL values are treated as a single matching group by GROUP BY, gathering all records with missing keys into one bucket.
Under the hood, database engines implement grouping using either hash-based aggregation or sort-based aggregation algorithms.
Hash aggregation builds an in-memory hash table of group keys, updating running aggregates as rows stream through.
Sort aggregation sorts the entire dataset by group keys first, then computes aggregates as it scans identical adjacent keys.
High-cardinality grouping keys, like unique transaction IDs, exhaust memory and degrade performance when grouped needlessly.
Analysts should choose grouping keys deliberately, matching the exact level of granularity required for business decisions.