Multi-Column Grouping

Overview

When you GROUP BY two or more columns, the database creates one group for each unique COMBINATION of values across those columns. A single-column GROUP BY country gives one row per country; adding a second column, e.g. GROUP BY country, status, gives one row per (country, status) pair. Against the shared dataset: SELECT c.country, o.status, COUNT(*) AS n FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.country, o.status ORDER BY c.country, o.status; This produces one row for each (country, status) combination that exists in the data. US has both 'paid' and 'pending' orders, so US gets two rows. Think of multi-column grouping as creating a grid: each axis is a grouping column, each cell that has data becomes a result row.

Real-world reports almost always need more than one dimension — revenue by country AND year, count by department AND status. Multi-column grouping is how you produce these cross-tabulated summaries.

Where used: cross-tab and pivot reports, multi-dimensional analytics, breakdown-by dashboards

Why learn this

• Building any report with two or more dimensions (e.g. sales by country and product category) requires multi-column GROUP BY
• Understanding how grouping key combinations work prevents confusion about why GROUP BY produces more rows than expected

Query walkthrough

SELECT c.country, o.status, COUNT(*) AS n
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.country IS NOT NULL
GROUP BY c.country, o.status
ORDER BY c.country, o.status;

Focus: Watch how 7 joined rows (after excluding Emma's NULL country) collapse into 5 groups when grouped by (country, status) — US splits into two rows because it has both paid and pending orders.

Common mistakes

• Expecting fewer rows than actual groups: GROUP BY country, status does NOT return one row per country — it returns one row per (country, status) combination. If a country has 3 different statuses, it appears in 3 rows. Count the unique combinations to predict row count.
• Grouping by too many columns accidentally: Adding a high-cardinality column (like customer id) to GROUP BY creates nearly one group per row, defeating the purpose of summarisation. Only include columns that represent the dimensions you actually want in the report.
• Forgetting that column order in GROUP BY does not affect groups: GROUP BY country, status produces the same groups as GROUP BY status, country — column order only matters for readability and in ORDER BY. However, many people confuse GROUP BY order with result ordering; always add an explicit ORDER BY.

Glossary

combination

A specific pairing or grouping of different values across multiple columns, like a unique country and status together.

dimension

A category or attribute used to organize and analyze data, like time, location, or product type.

summarisation

The process of condensing a large amount of detailed data into a shorter, aggregated form.

Recall questions

• How does GROUP BY a, b differ from GROUP BY a alone?
• Does changing the order of columns in GROUP BY (e.g. GROUP BY b, a vs GROUP BY a, b) change the resulting groups?

Understanding checks

What rows does this return?

AE | paid | 1 MX | paid | 1 UK | cancelled | 1 US | paid | 3 US | pending | 1

JOIN matches customers to their orders (excludes Greg/Hana with no orders). WHERE removes Emma (NULL country). The remaining 7 rows group by (country, status): AE-Farah has 1 paid; MX-Carlos has 1 paid; UK-Diana has 1 cancelled; US-paid has 3 (Alice orders 1,2 + Bob order 8); US-pending has 1 (Bob order 3).

If you changed this to GROUP BY category, price — how many rows would the result have?

6 rows — every product has a unique price, so each (category, price) combination maps to exactly one product. Multi-column grouping produces the same number of rows as the original table.

When the added grouping column has all unique values, every row becomes its own group. This demonstrates that adding high-cardinality columns to GROUP BY defeats summarisation.

Practice tasks

Add a second grouping dimension

This query counts orders by status only. Add customer_id as a second grouping column so you get one row per (customer_id, status) pair. Keep the ORDER BY matching the GROUP BY columns.

Challenge

Product category breakdown by order status

Write a query that shows each (category, status) combination with the total quantity of items ordered. Join products and order_items and orders. Order by category, then status.

Continue learning

Previous: GROUP BY

Previous: Aggregate Functions

Return to SQL Roadmap