Multiple Grouping Columns

Grouping by More Than One Column

GROUP BY accepts a comma-separated list of columns. Instead of one row per distinct value of a single column, you get one row per distinct combination of values across all listed columns.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Example

sql
SELECT region, product, SUM(amount) AS total_amount, COUNT(*) AS order_count
FROM orders
GROUP BY region, product
ORDER BY region, product;

Every unique (region, product) pair present in the data becomes its own group:

Six output rows for eight input rows — because there happen to be exactly six distinct (region, product) combinations in the data (East has both Widget rows 1 and 8 merged into East/Widget = 160; West has both Widget rows 3 and 4 merged into West/Widget = 250).

Column Order Matters for Meaning, Not for Grouping Result

GROUP BY region, product and GROUP BY product, region produce the same set of groups (grouping is set-based, unordered), but:

• The column order in GROUP BY has no effect on which rows end up together.
• It can interact with ORDER BY and with hierarchical extensions like ROLLUP (Topic 15.7), where column order defines the subtotal hierarchy.

The SELECT-List Rule, Extended

The same rule from Topic 15.1 applies with multiple columns: every non-aggregated column in SELECT must be listed in GROUP BY. Here, both region and product must appear in GROUP BY since both appear ungrouped in SELECT.

sql
-- Invalid: 'product' is selected but not grouped and not aggregated
SELECT region, product, SUM(amount) FROM orders GROUP BY region;

Edge Cases

• More grouping columns → more, smaller groups. Grouping by region alone gave 3 groups; adding product splits those into 6 (more granular, but each group has fewer/smaller aggregates — e.g. East's total of 310 splits into Gadget=150 and Widget=160).
• A combination with no matching rows never appears. There's no North/Gadget row missing here, but in general, GROUP BY only ever produces groups for combinations that exist in the data — it will not manufacture a (region, product) row for a pairing that has zero matching orders. (Contrast this with GROUPING SETS/CUBE in 15.6–15.8, which can surface subtotal rows that aggregate across one of the dimensions.)
• NULL combinations: if either grouping column can be NULL, rows with NULL in that column form their own group, combined with whatever the other column's value is.
• Too many columns → too many tiny groups: grouping by every column in a wide table degenerates toward one group per row, defeating the purpose of aggregation.

Key Takeaways / Interview Q&A

• Q: What does `GROUP BY region, product` produce?

A: One row per unique (region, product) combination actually present in the data — 6 rows here, not 3×3=9, because not every theoretical pairing exists.

• Q: Does the order of columns in GROUP BY change the result set?

A: No, the set of groups is identical either way; only ORDER BY (and hierarchical extensions like ROLLUP) care about column order.

• Q: What is East's Widget-only total in this data?

A: 160 (orders 1 and 8: 100 + 60).

• Q: If you add a third grouping column, what happens to group granularity?

A: Groups get smaller/more numerous — each additional grouping column can only split existing groups further, never merge them.