SQL: GROUP BY & HAVING
Master categorical grouping and group-level filtering. Learn multi-column grouping, aggregate calculations per group, the HAVING clause, WHERE vs HAVING, relational joins with aggregation, ROLLUP subtotals, and common GROUP BY pitfalls.
1. The GROUP BY Clause: Categorical Grouping
The GROUP BY clause collapses multiple rows sharing identical values in specified columns into summary categories. Instead of calculating a single global aggregate across the entire table, GROUP BY partitions your table into distinct buckets and calculates aggregate metrics for each category independently.
In the SQL logical order of execution, GROUP BY executes at Step 3, directly after table joins (Step 1) and WHERE row filtering (Step 2). This means that rows excluded by the WHERE clause are filtered out before groups are assembled.
Basic GROUP BY Categorization
SQLPartition students into departmental buckets and count members in each group
How the Query Planner Groups Records
Relational database engines use two primary algorithmic strategies to group rows:
- Hash Aggregation:The engine builds an in-memory hash table where unique grouping column values serve as keys, accumulating aggregate counts as rows are scanned. Extremely fast for unsorted inputs.
- Stream / Sort Aggregation:If rows are already sorted (such as via a B-Tree index on the grouping column), the engine reads the index sequentially and outputs group totals immediately when the key value transitions.
2. GROUP BY with Aggregate Functions
When you group by a category, every other column projected in the SELECT list must either be part of the GROUP BY clause or wrapped inside an aggregate function (COUNT, AVG, SUM, MIN, MAX).
Multi-Metric Departmental Aggregation
SQLCalculate counts, averages, and extremes simultaneously within departmental buckets
NULL Values in GROUP BY
How the GROUP BY clause treats missing values:
- Single NULL Group:If a grouping column contains NULL values across multiple rows, all rows with NULL are grouped together into a single category bucket.
- Aggregates Inside the NULL Group:Aggregate functions compute values for the NULL bucket identically to named categories.
3. GROUP BY Multiple Columns (Multi-Level Grouping)
You can group by two or more columns by separating them with commas. When grouping by multiple columns, a distinct group is generated for each unique combination of the specified grouping values.
Hierarchical Grouping Across Multiple Columns
SQLAssemble groups for each unique department-and-city combination
Column Order in GROUP BY
How grouping column order affects query results:
- Identical Bucket Counts:GROUP BY department, city produces the exact same statistical groups as GROUP BY city, department. The grouping logic is commutative.
- Index Utilization:If an index exists on (department, city), specifying GROUP BY department, city allows the database to use index-ordered stream aggregation without temporary hash tables.
4. The HAVING Clause: Filtering Aggregated Groups
The HAVING clause filters result groups after they have been assembled by GROUP BY. Because the WHERE clause executes before groups are formed, you cannot use WHERE to filter aggregated metrics like COUNT(*) > 2 or AVG(gpa) >= 3.5.
In the SQL logical order of execution, HAVING evaluates at Step 4, immediately following GROUP BY. Only groups satisfying the HAVING boolean expression are retained for the final SELECT projection.
Filtering Groups with HAVING
SQLRetain only departmental groups satisfying minimum headcount and GPA thresholds
HAVING Without GROUP BY
What happens when you write HAVING without a GROUP BY clause:
- Global Table Group:If you write SELECT AVG(gpa) FROM students HAVING AVG(gpa) > 3.0, the entire table is treated as a single implicit group.
- All or Nothing:If the condition passes, the single aggregated row is returned; if it fails, zero rows are returned.
5. WHERE vs HAVING: Direct Comparison
Choosing whether to place a filter condition in WHERE or HAVING is a fundamental database design decision that directly impacts query performance:
| Evaluation Criteria | WHERE Clause | HAVING Clause |
|---|---|---|
| Execution Step | Step 2 (Before GROUP BY) | Step 4 (After GROUP BY) |
| Filtering Scope | Filters individual row records | Filters aggregated group categories |
| Aggregate Functions Allowed | No (e.g. WHERE AVG(gpa) > 3 is invalid) | Yes (e.g. HAVING COUNT(*) > 5 is valid) |
| Index Utilization | High (uses B-Tree indexes to prune rows early) | Low (evaluates after aggregation in RAM) |
| Performance Impact | Reduces data volume before expensive grouping | Evaluates only against aggregated group count |
Combining WHERE and HAVING Efficiently
SQLFilter individual rows before grouping, then filter assembled group aggregates
Performance Golden Rule: Prune Early with WHERE
Never place row-level filters inside a HAVING clause:
- Anti-Pattern:Writing GROUP BY department HAVING department = 'Engineering' groups millions of rows across the entire table before discarding all non-engineering groups.
- Optimal Pattern:Writing WHERE department = 'Engineering' GROUP BY department prunes unwanted records immediately using indexes, reducing work volume by 90%.
6. GROUP BY with ORDER BY
By default, grouped result sets return in arbitrary category order. To sort grouped categories deterministically, add an ORDER BY clause. You can sort by the grouping column itself or by the calculated aggregate metric.
Sorting Groups by Aggregate Metrics
SQLOrder summary categories from most populated to least populated
Aliases in ORDER BY with GROUP BY
Why aliases work in ORDER BY but fail in HAVING across some engines:
- ORDER BY Evaluates Last:Because ORDER BY runs at Step 7 after SELECT (Step 5), you can sort directly by column aliases like ORDER BY total_students DESC.
- HAVING Evaluates Before SELECT:In strict ANSI SQL and PostgreSQL, HAVING executes at Step 4 before aliases exist. You must write HAVING COUNT(*) >= 2 rather than HAVING student_count >= 2.
- MySQL Exception:MySQL permits column aliases inside HAVING clauses as an engine-specific extension.
7. GROUP BY with JOIN (Relational Aggregation)
In normalized relational schemas, analytical questions often require joining parent and child tables before grouping (for example, counting how many orders each customer has placed, or summing total revenue per category).
Relational Aggregation with LEFT JOIN
SQLJoin parent students with child course enrollments and aggregate credits per student
The LEFT JOIN + COUNT(col) Principle
When aggregating over a LEFT JOIN to include parents with zero child rows:
- COUNT(*) Trap:Writing COUNT(*) on a LEFT JOIN returns 1 for students with zero courses, because the outer join produces a row with NULL child columns!
- COUNT(c.enrollment_id) Remedy:Always count a non-null child column: COUNT(c.enrollment_id). Since NULL child columns are ignored, students with zero courses report 0 correctly.
8. Advanced Aggregation: ROLLUP, CUBE, and GROUPING SETS
Enterprise reporting queries often require computing multi-level hierarchical subtotals and grand totals in a single query pass. ANSI SQL provides specialized extensions for dimensional reporting:
| Operator | Syntax Example | Generated Subtotal Levels |
|---|---|---|
| ROLLUP | GROUP BY ROLLUP (year, region) | Hierarchical subtotals: (year, region) + (year) + () grand total |
| CUBE | GROUP BY CUBE (year, region) | All permutations: (year, region) + (year) + (region) + () grand total |
| GROUPING SETS | GROUP BY GROUPING SETS ((year), (region)) | Explicit selective subtotal levels without generating cross products |
Hierarchical Subtotals and Grand Totals
SQLObserve multi-level aggregation output generating category subtotals and grand totals
The GROUPING() Function
Identifying generated subtotal rows in dimensional reporting:
- The Function:GROUPING(column) returns 1 if the column is null because of a subtotal aggregation row, and 0 if the value is part of the original row data.
- Labeling Totals:Use CASE WHEN GROUPING(region) = 1 THEN 'All Regions' ELSE region END to provide clean executive report labels.
9. Common GROUP BY Errors and Anti-Patterns
Database engines enforce strict rules to prevent non-deterministic aggregation. Here are the two most common errors developers encounter:
Resolving the Non-Aggregated Column Error
SQLProjecting multiple row values per group requires aggregation with GROUP_CONCAT
MySQL ONLY_FULL_GROUP_BY Mode
A frequent source of bugs when migrating between MySQL and other databases:
- Legacy MySQL Behavior:Historically, MySQL allowed queries like SELECT department, name FROM students GROUP BY department, silently picking an arbitrary name from the group.
- Modern Standard Compliance:Starting in MySQL 5.7+, ONLY_FULL_GROUP_BY is enabled by default, matching PostgreSQL, Oracle, and SQL Server in rejecting non-aggregated columns.
- The Rule of Thumb:Every column in your SELECT list must either be listed in the GROUP BY clause or enclosed in an aggregate function.
Knowledge Check: SQL GROUP BY & HAVING
1. When does the HAVING clause execute in the SQL query lifecycle?
2. What is the primary operational difference between WHERE and HAVING?
3. What error occurs if you project a non-aggregated column omitted from GROUP BY?
4. What does the ROLLUP operator generate in addition to standard groups?
5. Can you use aggregate expressions like COUNT(*) inside a WHERE clause?
6. In GROUP BY department, city, how are rows grouped?