SQL GROUP BY Multiple Columns, With Examples
· 7 min read
To group by multiple columns in SQL, list them in GROUP BY separated by commas. SQL then returns one row for every combination of those columns that exists in the data, and each aggregate (SUM, COUNT, AVG) is calculated within that combination.
SELECT region, channel, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY region, channel
ORDER BY region, channel;Every column in SELECT that is not inside an aggregate must also be in GROUP BY. Below: a worked example, how GROUP BY and ORDER BY work together, grouping after a join, and the mistakes that produce plausible but wrong totals.
A worked example
| order_id | customer_id | region | channel | amount |
|---|---|---|---|---|
| 1 | 1 | North | Online | 120 |
| 2 | 2 | North | Online | 80 |
| 3 | 1 | North | Phone | 200 |
| 4 | 3 | South | Online | 50 |
| 5 | 3 | South | Phone | 150 |
| 6 | 4 | South | Phone | 100 |
| 7 | 2 | North | Online | 40 |
The query above returns:
| region | channel | orders | revenue |
|---|---|---|---|
| North | Online | 3 | 240 |
| North | Phone | 1 | 200 |
| South | Online | 1 | 50 |
| South | Phone | 2 | 250 |
Seven order rows collapsed into four groups, one per region and channel pair. North Online is orders 1, 2 and 7: 120 + 80 + 40 = 240.
Compare with SQL GROUP BY on one column
With a single column, you get one row per region and the channel detail disappears:
SELECT region, SUM(amount) AS revenue
FROM orders
GROUP BY region
ORDER BY region;| region | revenue |
|---|---|
| North | 440 |
| South | 300 |
Adding a column to GROUP BY never changes the grand total. It only splits each group into smaller pieces. North's 440 is the 240 + 200 from the first result.
Does column order in GROUP BY matter?
Not for the result. GROUP BY region, channel and GROUP BY channel, region produce the same groups and the same numbers. What controls the order of the rows you see is ORDER BY.
GROUP BY and ORDER BY
GROUP BY decides which rows end up together. ORDER BY decides how the finished groups are sorted. It runs last, so it can sort by an aggregate or its alias:
SELECT region, channel, SUM(amount) AS revenue
FROM orders
GROUP BY region, channel
ORDER BY revenue DESC;| region | channel | revenue |
|---|---|---|
| South | Phone | 250 |
| North | Online | 240 |
| North | Phone | 200 |
| South | Online | 50 |
The clauses always appear in this order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. If you need to filter on the aggregate (only groups with revenue over 200, say), that is a job for HAVING: see HAVING vs WHERE.
GROUP BY 1, 2
Postgres, MySQL, DuckDB and SQLite let you refer to SELECT columns by position:
SELECT region, channel, SUM(amount) AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;It returns the same four rows as the first query. It is handy for long CASE expressions, but it breaks silently if someone reorders the SELECT list. SQL Server does not support it in GROUP BY.
SQL JOIN with GROUP BY
Often the column you want to group by lives in another table. Join first, then group by columns from either table.
| customer_id | customer_name | segment |
|---|---|---|
| 1 | Acme Dental | Enterprise |
| 2 | Birch Cafe | Small Business |
| 3 | Cobalt Labs | Small Business |
| 4 | Dune Studio | Enterprise |
SELECT c.segment, o.channel, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.segment, o.channel
ORDER BY c.segment, o.channel;| segment | channel | revenue |
|---|---|---|
| Enterprise | Online | 120 |
| Enterprise | Phone | 300 |
| Small Business | Online | 170 |
| Small Business | Phone | 150 |
SQL runs the join first, so each order row now carries its customer's segment, and only then forms the groups. If the join itself is new to you, see how to join two tables.
Subtotals with ROLLUP
To get subtotals per region plus a grand total in one query, GROUP BY ROLLUP (region, channel) works in Postgres, DuckDB, SQL Server and Oracle (MySQL writes it GROUP BY region, channel WITH ROLLUP):
SELECT region, channel, SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP (region, channel)
ORDER BY region NULLS LAST, channel NULLS LAST;| region | channel | revenue |
|---|---|---|
| North | Online | 240 |
| North | Phone | 200 |
| North | NULL | 440 |
| South | Online | 50 |
| South | Phone | 250 |
| South | NULL | 300 |
| NULL | NULL | 740 |
The NULL rows are the subtotals and the grand total.
Common mistakes
Averaging at the wrong level after a join (the AI mistake to watch for)
Ask an AI assistant for "average order value by segment" when the amounts live in an order_lines table, and it will often join everything and call AVG on the line amounts:
| order_id | line_no | line_amount |
|---|---|---|
| 1 | 1 | 100 |
| 1 | 2 | 20 |
| 2 | 1 | 80 |
| 3 | 1 | 50 |
| 3 | 2 | 50 |
| 3 | 3 | 100 |
| 4 | 1 | 50 |
| 5 | 1 | 90 |
| 5 | 2 | 60 |
| 6 | 1 | 100 |
| 7 | 1 | 40 |
SELECT c.segment, AVG(l.line_amount) AS avg_order_value
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_lines AS l ON l.order_id = o.order_id
GROUP BY c.segment
ORDER BY c.segment;| segment | avg_order_value |
|---|---|
| Enterprise | 70 |
| Small Business | 64 |
That is the average line, not the average order. Enterprise placed three orders worth 120, 200 and 100, so its average order is 140. Group by order first, then average the order totals:
SELECT segment, AVG(order_total) AS avg_order_value
FROM (
SELECT o.order_id, c.segment, SUM(l.line_amount) AS order_total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_lines AS l ON l.order_id = o.order_id
GROUP BY o.order_id, c.segment
) AS per_order
GROUP BY segment
ORDER BY segment;| segment | avg_order_value |
|---|---|
| Enterprise | 140 |
| Small Business | 80 |
How to spot it: ask "what does one row mean right before the AVG?" If the answer is "one order line" but the question says "per order", the query needs a grouping step first. A quick sanity check: work out one group by hand from three or four rows.
Selecting a column that is not grouped
SELECT region, channel, SUM(amount) AS revenue
FROM orders
GROUP BY region;DuckDB and Postgres refuse to run this: DuckDB says column "channel" must appear in the GROUP BY clause or must be part of an aggregate function. Older MySQL setups and SQLite run it and pick a channel from somewhere in each group, which is worse because you get an answer that looks real. Add the column to GROUP BY or remove it from SELECT.
Grouping by too much
Adding order_id or a date-time column to GROUP BY "to be safe" gives one row per order, so nothing is actually summarised. If your grouped result has almost as many rows as the raw table, check the GROUP BY list.
FAQ
How do I group by two columns in SQL?
Write both after GROUP BY with a comma: GROUP BY region, channel. Put the same two columns in SELECT so you can see which group each row is.
Can you GROUP BY a column that is not in SELECT?
Yes. SELECT region, SUM(amount) FROM orders GROUP BY region, channel is valid, but the output has two North rows with no way to tell them apart. It is almost always clearer to select every grouped column.
Does GROUP BY come before or after WHERE?
After. WHERE filters rows first, then GROUP BY builds groups from what is left, then HAVING filters the groups.
What is the difference between GROUP BY and DISTINCT?
SELECT DISTINCT region, channel returns the same four combinations as the grouped query, but it cannot calculate anything per group. Use GROUP BY when you need a count, sum or average. For counting unique values, see COUNT DISTINCT.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Products per category” →