Grouping by Multiple Columns: Basics
What you'll learn: How to use GROUP BY with multiple columns to create nested subgroups based on unique combinations of values.
The Core Idea
When you include multiple columns in a GROUP BY clause, SQL creates a separate group for every unique combination of values across those columns. Think of it like organizing a library first by genre, then by author within each genre—you get multiple nested levels of organization.
For example, if you group by department and job_title, you won't just get one row per department or one row per job title. Instead, you'll get one row for each unique pairing: (Sales, Manager), (Sales, Associate), (HR, Manager), (HR, Associate), and so on.
Why This Matters
Multi-column grouping lets you answer more detailed questions:
- Single column: "What's the average salary per department?"
- Multiple columns: "What's the average salary for each job title within each department?"
Basic Syntax
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;
This creates groups for every unique (department, job_title) pair in your data. If you have 3 departments and 4 job titles each, you could have up to 12 groups (assuming all combinations exist).
The Order Doesn't Change Grouping
Listing columns in GROUP BY department, job_title produces the same groups as GROUP BY job_title, department—just potentially in a different display order when combined with sorting.
Key Takeaway: GROUP BY with multiple columns creates granular subgroups for each unique combination of those column values, enabling detailed multi-dimensional analysis of your data.