Grouping by Multiple Columns
What you'll learn: How to create nested, hierarchical groupings in SQL by specifying more than one column in your GROUP BY clause.
The Core Idea
When you use GROUP BY with a single column, SQL organizes rows into groups based on unique values in that column. But what if you want subcategories within categories? That's where grouping by multiple columns comes in.
By listing multiple columns in GROUP BY, you create a hierarchy of groupings—rows are first grouped by the first column, then within each of those groups, they're subdivided by the second column, and so on.
Think of It Like Organization
Imagine organizing books:
- Single column: Group by genre (all Fiction together, all Science together)
- Multiple columns: Group by genre, then by author within each genre
SQL works the same way. Each unique combination of values across all grouped columns forms one group.
Basic Syntax
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2;
This creates a separate group for every unique pairing of column1 and column2 values.
Practical Example
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;
This shows average salary for each job title within each department—not just by department overall. You get results like:
- Sales, Manager: $75,000
- Sales, Associate: $45,000
- IT, Manager: $85,000
- IT, Developer: $70,000
The order matters for readability (department before title feels natural), but mathematically produces the same groups.
Key Takeaway: Grouping by multiple columns creates one aggregate group for each unique combination of values across all specified columns, enabling hierarchical analysis of your data.