SQL - GROUP BY Multiple Columns
GROUP BY is fundamental to SQL analytics, but single-column grouping only gets you so far. Real business questions rarely fit into one dimension. You don't just want total sales—you want sales by...
Key Insights
- GROUP BY with multiple columns creates unique combinations of values, treating each distinct tuple as a separate group for aggregation
- Column order in GROUP BY doesn’t affect results but can impact query readability and should match your logical data hierarchy
- Always ensure every non-aggregated column in SELECT appears in GROUP BY, and use HAVING (not WHERE) to filter on aggregated results
Introduction
GROUP BY is fundamental to SQL analytics, but single-column grouping only gets you so far. Real business questions rarely fit into one dimension. You don’t just want total sales—you want sales by region and product category. You don’t just want employee counts—you want them by department and job title and location.
Multi-column GROUP BY lets you slice data across multiple dimensions simultaneously. It’s the difference between a flat report and a pivot table. Once you understand how SQL handles these combinations, you’ll write cleaner aggregation queries and build more insightful reports.
Syntax and Basic Usage
The syntax is straightforward: list multiple columns after GROUP BY, separated by commas. Every non-aggregated column in your SELECT must appear in the GROUP BY clause.
SELECT
region,
product_category,
COUNT(*) AS order_count,
SUM(amount) AS total_sales
FROM sales
GROUP BY region, product_category
ORDER BY region, product_category;
This query groups sales by every unique combination of region and product category. If you have 4 regions and 5 product categories, you could have up to 20 groups (fewer if some combinations don’t exist in your data).
Column order in GROUP BY doesn’t change your results. GROUP BY region, product_category produces the same groups as GROUP BY product_category, region. However, I recommend ordering columns from broadest to most specific—it makes your queries easier to read and matches how you’d naturally describe the grouping: “sales by region, then by category.”
-- These produce identical results
SELECT region, product_category, SUM(amount)
FROM sales
GROUP BY region, product_category;
SELECT region, product_category, SUM(amount)
FROM sales
GROUP BY product_category, region;
How Multi-Column Grouping Works
SQL creates groups by treating each unique combination of column values as a single unit. Think of it as concatenating the values into a composite key. Every row with the same composite key lands in the same group.
Let’s compare single-column and multi-column grouping to see this clearly:
-- Sample data in 'sales' table:
-- | region | product_category | amount |
-- |--------|------------------|--------|
-- | East | Electronics | 500 |
-- | East | Electronics | 300 |
-- | East | Clothing | 200 |
-- | West | Electronics | 400 |
-- | West | Clothing | 150 |
-- Single-column GROUP BY
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region;
-- Result:
-- | region | total |
-- |--------|-------|
-- | East | 1000 |
-- | West | 550 |
-- Multi-column GROUP BY
SELECT region, product_category, SUM(amount) AS total
FROM sales
GROUP BY region, product_category;
-- Result:
-- | region | product_category | total |
-- |--------|------------------|-------|
-- | East | Electronics | 800 |
-- | East | Clothing | 200 |
-- | West | Electronics | 400 |
-- | West | Clothing | 150 |
The single-column query produces 2 groups (one per region). The multi-column query produces 4 groups (one per region-category combination). Each row is assigned to exactly one group based on its combination of values.
This is conceptually similar to creating a compound key. SQL internally treats ('East', 'Electronics') as different from ('East', 'Clothing') and ('West', 'Electronics').
Common Aggregate Functions with Multiple Groups
All standard aggregate functions work identically with multi-column grouping. You can combine multiple aggregates in a single query to build comprehensive summaries.
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(QUARTER FROM order_date) AS order_quarter,
COUNT(*) AS order_count,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(total_amount) AS revenue,
AVG(total_amount) AS avg_order_value,
MIN(total_amount) AS smallest_order,
MAX(total_amount) AS largest_order
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY
EXTRACT(YEAR FROM order_date),
EXTRACT(QUARTER FROM order_date)
ORDER BY order_year, order_quarter;
This produces a quarterly breakdown with multiple metrics per period:
| order_year | order_quarter | order_count | unique_customers | revenue | avg_order_value | smallest_order | largest_order |
|------------|---------------|-------------|------------------|------------|-----------------|----------------|---------------|
| 2023 | 1 | 1542 | 892 | 234567.00 | 152.12 | 12.50 | 2340.00 |
| 2023 | 2 | 1687 | 945 | 267891.00 | 158.80 | 8.99 | 3100.00 |
| 2023 | 3 | 1823 | 1021 | 298456.00 | 163.72 | 15.00 | 2890.00 |
| 2023 | 4 | 2156 | 1198 | 356789.00 | 165.47 | 9.99 | 4500.00 |
Notice that expressions like EXTRACT(YEAR FROM order_date) must appear in both SELECT and GROUP BY. Some databases allow column aliases in GROUP BY, but for maximum compatibility, repeat the expression.
Filtering Grouped Results with HAVING
WHERE filters rows before grouping. HAVING filters groups after aggregation. This distinction matters when you need conditions on aggregated values.
-- Find region/category combinations with significant sales volume
SELECT
region,
product_category,
COUNT(*) AS order_count,
SUM(amount) AS total_sales,
AVG(amount) AS avg_sale
FROM sales
WHERE order_date >= '2024-01-01' -- Filter rows first
GROUP BY region, product_category
HAVING
SUM(amount) > 10000 -- Then filter groups
AND COUNT(*) >= 50
ORDER BY total_sales DESC;
You can combine multiple HAVING conditions. A common pattern is filtering for both minimum volume and minimum count to ensure statistical significance:
SELECT
customer_segment,
product_line,
AVG(satisfaction_score) AS avg_satisfaction,
COUNT(*) AS response_count
FROM customer_feedback
GROUP BY customer_segment, product_line
HAVING
COUNT(*) >= 30 -- Minimum sample size
AND AVG(satisfaction_score) < 3.5 -- Below threshold
ORDER BY avg_satisfaction;
This finds underperforming segment/product combinations with enough data to be meaningful.
Practical Use Cases
Employee Compensation Analysis
HR teams frequently need salary breakdowns across multiple dimensions:
SELECT
department,
job_title,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary,
ROUND(MAX(salary) - MIN(salary), 2) AS salary_spread
FROM employees
WHERE employment_status = 'Active'
GROUP BY department, job_title
HAVING COUNT(*) >= 3 -- Privacy: only show groups with 3+ employees
ORDER BY department, avg_salary DESC;
Order Status by Customer Segment
E-commerce platforms track order fulfillment across customer tiers:
SELECT
c.customer_tier,
o.order_status,
COUNT(*) AS order_count,
ROUND(AVG(EXTRACT(EPOCH FROM (o.shipped_date - o.order_date)) / 3600), 1)
AS avg_hours_to_ship
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.customer_tier, o.order_status
ORDER BY c.customer_tier, order_count DESC;
Time-Series with Categorical Breakdown
Dashboard queries often combine time dimensions with categories:
SELECT
DATE_TRUNC('week', event_timestamp) AS week_start,
event_type,
platform,
COUNT(*) AS event_count,
COUNT(DISTINCT user_id) AS unique_users
FROM user_events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY
DATE_TRUNC('week', event_timestamp),
event_type,
platform
ORDER BY week_start, event_type, platform;
Performance Considerations and Best Practices
Index Your GROUP BY Columns
Indexes dramatically improve GROUP BY performance. The database can use an index to avoid sorting when grouping.
-- Create a composite index matching your common GROUP BY pattern
CREATE INDEX idx_sales_region_category
ON sales(region, product_category);
-- This query can now use the index efficiently
SELECT region, product_category, SUM(amount)
FROM sales
GROUP BY region, product_category;
For composite indexes, column order matters. An index on (region, product_category) helps queries that group by region alone or by both columns, but won’t help queries that only group by product_category.
Minimize Columns in GROUP BY
Only include columns you actually need. Extra columns create more groups and more work:
-- Inefficient: grouping by unnecessary columns
SELECT department, COUNT(*)
FROM employees
GROUP BY department, employee_id, hire_date; -- Creates one group per employee!
-- Correct: only group by what you need
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Filter Early with WHERE
Reduce the dataset before grouping whenever possible:
-- Better: filter first, then group
SELECT region, product_category, SUM(amount)
FROM sales
WHERE order_date >= '2024-01-01' -- Reduces rows before grouping
GROUP BY region, product_category;
-- Worse: grouping all data, then filtering
SELECT region, product_category, SUM(amount)
FROM sales
GROUP BY region, product_category
HAVING MIN(order_date) >= '2024-01-01'; -- Processes all rows first
Check Execution Plans
Use EXPLAIN to verify your queries use indexes effectively:
EXPLAIN ANALYZE
SELECT region, product_category, SUM(amount)
FROM sales
GROUP BY region, product_category;
Look for “Index Scan” or “Index Only Scan” rather than “Seq Scan” on large tables. If you see “Sort” operations, consider whether an index could eliminate them.
Multi-column GROUP BY is a workhorse of SQL analytics. Master the mechanics—unique combinations, HAVING vs WHERE, index alignment—and you’ll write queries that are both correct and fast. Start with clarity, then optimize based on actual query plans and performance measurements.