SQL / AGGREGATION WITH GROUP BY
Grouping by more than one column
Aggregate on a compound key with GROUP BY a, b: read a group as a combination, predict how many rows come back, and see why missing pairs vanish.
What you will learn
- Write GROUP BY a, b to get one row per distinct combination of a and b
- Predict that adding a grouping column only splits groups, never merges them
- Know that a combination absent from the data produces no row at all, not a zero
- Keep every non-aggregated column of the SELECT list inside GROUP BY
Understanding Grouping by more than one column
GROUP BY region, quarter does not group twice. It builds one group per distinct pair of values, so two rows land together only if they agree on region and on quarter at the same time. The set of groups comes from the data, not from the column definitions: if the West region never recorded a Q2 sale, there is no West/Q2 group and therefore no West/Q2 row in the result. Each aggregate is then computed over just the rows sitting inside one of those pairs.
The mental model that pays off is subdivision. Grouping by region alone puts every East row in one bucket; appending quarter cuts that bucket into as many pieces as there are distinct quarters present within East. Group count can only stay the same or grow when you append a column, and it stays the same exactly when the new column draws no new distinction inside any existing group. Every input row still belongs to exactly one group, which is why SUM(amount) over the grouped result always adds back up to the ungrouped SUM(amount).
Because the group key is now a tuple, everything in SELECT must either be part of that tuple or wrapped in an aggregate: the engine has no single value to show for a column that varies inside a group. That is why you routinely see GROUP BY c.id, c.name, where the name adds no new groups since one id has one name, but listing it is what makes displaying it legal. The order of the columns in GROUP BY has no effect on which groups form or what the aggregates equal; a compound grouping yields an unordered set of rows, and only ORDER BY makes it read like a nested report.
CREATE TABLE sales (
region TEXT,
quarter TEXT,
channel TEXT,
amount INTEGER
);
INSERT INTO sales VALUES
('East', 'Q1', 'web', 100),
('East', 'Q1', 'store', 50),
('East', 'Q2', 'web', 200),
('West', 'Q1', 'web', 75),
('West', 'Q1', 'web', 25),
('West', 'Q3', 'store', 300);
SELECT region, quarter, COUNT(*) AS orders, SUM(amount) AS total
FROM sales
GROUP BY region, quarter
ORDER BY region, quarter;The group key is the whole combination of listed columns, so each extra column subdivides existing groups and only combinations that occur in the data become rows.
Worked examples
Adding a third grouping column
Shows how one more column in GROUP BY splits some groups and leaves others untouched.
SELECT region, quarter, channel, SUM(amount) AS total
FROM sales
GROUP BY region, quarter, channel
ORDER BY region, quarter, channel;Example explained
Line 1The East/Q1 group of 150 splits into store (50) and web (100), because its two rows disagree on the new column.
Line 2West/Q1 stays a single row at 100: both of its rows are web, so channel adds no distinction there.
Line 3Five rows instead of four, but the total column still adds up to 750, the same grand total as before.
Line 4ORDER BY repeats the grouping columns so the output reads region, then quarter, then channel.
Grouping across a join
Demonstrates a compound key where one column is determined by another and adds no extra groups.
CREATE TABLE customer (id INTEGER, name TEXT);
CREATE TABLE payment (customer_id INTEGER, method TEXT, cents INTEGER);
INSERT INTO customer VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO payment VALUES
(1, 'card', 500),
(1, 'card', 250),
(1, 'cash', 100),
(2, 'card', 900);
SELECT c.id, c.name, p.method, COUNT(*) AS payments, SUM(p.cents) AS cents
FROM customer c
JOIN payment p ON p.customer_id = c.id
GROUP BY c.id, c.name, p.method
ORDER BY c.id, p.method;Example explained
Line 1GROUP BY c.id, c.name, p.method makes the key a three-part tuple, giving one row per customer and method.
Line 2c.name creates no extra groups because each id has exactly one name; it is listed only so the SELECT may display it.
Line 3Ada's two card payments collapse into one row with COUNT(*) = 2 and SUM = 750; her cash payment is a separate group.
Line 4Grouping runs after the join, so COUNT(*) counts joined payment rows, not customers.
Important notes
NULL counts as one value while grouping, so all rows with a NULL channel inside the same region and quarter form a single group, even though NULL = NULL never evaluates to true.
GROUP BY region, quarter and GROUP BY quarter, region return identical result sets; grouping never sorts anything, so keep an explicit ORDER BY.
Common mistakes
Selecting a column that is not in the group key, such as SELECT region, quarter, channel with GROUP BY region, quarter. Postgres and MySQL with ONLY_FULL_GROUP_BY reject it, while SQLite returns the channel of one arbitrary row per group, so the report looks plausible and is wrong.
Expecting every region and quarter pairing to appear. A quarter with no sales in a region yields no row, so a grid or chart that needs a value per cell shows a hole; you have to build the pairs yourself with a cross join and LEFT JOIN onto the aggregate.
Dropping a near-unique column like order_id or a full timestamp into the GROUP BY list. Each group shrinks to one row, COUNT(*) is 1 everywhere, and the query becomes a slow copy of the table.
Try it yourself
Change, predict, then run
Using the sales table above, write one query returning one row per channel and quarter with COUNT(*) and SUM(amount). Then run the same query grouped by channel alone and explain why the row counts differ.
Open the SQL workspaceCheck your understanding
A table has 2 distinct regions and 4 distinct quarters, and GROUP BY region returns 2 rows. What can you say for certain about the row count of GROUP BY region, quarter?
- Between 2 and 8, depending on which pairs actually occur in the data
- Exactly 8, one row for every region and quarter pairing
- Exactly 4, since quarter has the higher number of distinct values
- Still 2, because region already determines how the rows are grouped
Show answer
Each region has at least one row and so contributes at least one pair, giving a floor of 2, and there are at most 2 x 4 = 8 possible pairs, giving a ceiling of 8. Exactly 8 is only true if every pairing occurs; a region with no sales in some quarter simply produces no row for it, because groups are the distinct combinations found in the data rather than a cross product of the column values.