SQL / AGGREGATION WITH GROUP BY
Grouping rows with GROUP BY
Write single-column GROUP BY queries that collapse rows into one row per distinct key, and explain why each selected column must be a key or an aggregate.
What you will learn
- Predict a grouped query's row count: one row per distinct key value in the data
- Build SELECT lists where every item is the grouping key or an aggregate call
- Put row-level conditions in WHERE, knowing they run before any group is formed
- Expect NULL keys to form one group and add ORDER BY when order matters
Understanding Grouping rows with GROUP BY
GROUP BY splits the rows that survive WHERE into buckets, one bucket per distinct value of the grouping column, and then runs each aggregate once per bucket. The result therefore has one row per bucket: six shipments spread over three carriers produce three rows, and the number of input rows has no bearing on the number of output rows. If you can finish the sentence "one row per ..." for the question you are answering, you have already chosen the GROUP BY column.
Once rows are bucketed, a bucket holds many rows but produces a single output row, so every expression in SELECT must yield exactly one value per bucket. Two kinds of expressions qualify: the grouping key, which is identical for every row in its bucket by construction, and aggregate calls such as COUNT(*) or SUM(weight_kg), which reduce many values to one. Naming any other bare column asks the engine to pick one weight out of three with no rule for choosing, so PostgreSQL rejects the query while SQLite and MySQL in its default mode return a value from an arbitrary row, which is worse because the query still appears to work.
Clauses are evaluated in a fixed logical order (FROM, WHERE, GROUP BY, aggregates, SELECT, ORDER BY), and most GROUP BY confusion comes from ignoring it. WHERE runs before any bucket exists, so it filters individual rows and cannot refer to COUNT(*); ORDER BY runs after aggregation, so it may sort by an aggregate or by an alias defined in SELECT. Grouping itself promises nothing about output order even when a sort-based execution plan makes the rows come back sorted, so state ORDER BY whenever the order is part of the answer.
CREATE TABLE shipments (
id INTEGER,
carrier TEXT,
weight_kg REAL
);
INSERT INTO shipments (id, carrier, weight_kg) VALUES
(1, 'DHL', 2.5),
(2, 'UPS', 1.5),
(3, 'DHL', 4.25),
(4, 'FedEx', 3.75),
(5, 'UPS', 2.0),
(6, 'DHL', 0.5);
SELECT carrier,
COUNT(*) AS shipments,
SUM(weight_kg) AS total_kg
FROM shipments
GROUP BY carrier
ORDER BY carrier;GROUP BY replaces many rows with one row per distinct key value, which is why every selected column must either be constant within the group or aggregated over it.
Worked examples
WHERE runs before the groups exist
Rows removed by WHERE never reach the grouping step, so a carrier can disappear from the result entirely instead of showing a zero.
CREATE TABLE deliveries (carrier TEXT, weight_kg REAL);
INSERT INTO deliveries (carrier, weight_kg) VALUES
('DHL', 2.5),
('DHL', 0.5),
('UPS', 1.0),
('Post', 0.3);
SELECT carrier, COUNT(*) AS heavy_count
FROM deliveries
WHERE weight_kg >= 1.0
GROUP BY carrier
ORDER BY carrier;Example explained
Line 1WHERE weight_kg >= 1.0 is applied first, so only three of the four rows are ever bucketed.
Line 2DHL reports 1 rather than 2 because its 0.5 kg row was discarded before the bucket was built.
Line 3'Post' has no surviving row, so no bucket is created for it: GROUP BY never invents a group with count 0.
Line 4To show 'Post' with a 0 you would need a list of carriers and an outer join, not a change to GROUP BY.
NULL is a group of its own
Rows with a NULL grouping key are collected into one group, unlike NULL comparisons in WHERE which are never true.
CREATE TABLE signups (email TEXT, source TEXT);
INSERT INTO signups (email, source) VALUES
('a@example.com', 'ads'),
('b@example.com', NULL),
('c@example.com', 'ads'),
('d@example.com', NULL),
('e@example.com', 'referral'),
('f@example.com', NULL);
SELECT source, COUNT(*) AS n
FROM signups
GROUP BY source
ORDER BY COUNT(*) DESC;Example explained
Line 1GROUP BY source builds one bucket per distinct value, and all three NULL rows land in a single NULL bucket even though NULL = NULL is never true.
Line 2COUNT(*) then reports 3 for that bucket, which is the number of signups with an unknown source, not an absence of rows.
Line 3ORDER BY COUNT(*) DESC is allowed because ORDER BY is evaluated after the aggregates have been computed.
Line 4How the NULL key is printed is a client decision (blank in the sqlite3 shell, NULL in most GUI grids); the group is there either way.
No GROUP BY means one implicit group
The same aggregates over the same table, first as a single group covering every row, then as one group per station.
CREATE TABLE readings (station TEXT, celsius REAL);
INSERT INTO readings (station, celsius) VALUES
('north', 10.0),
('north', 13.0),
('south', 20.5),
('south', 25.0);
SELECT COUNT(*) AS n, AVG(celsius) AS avg_c
FROM readings;
SELECT station, COUNT(*) AS n, AVG(celsius) AS avg_c
FROM readings
GROUP BY station
ORDER BY station;Example explained
Line 1The first query has no GROUP BY, so the whole table is one implicit group and the result is exactly one row no matter how many rows are stored.
Line 2GROUP BY station replaces that single group with one group per distinct station, so the identical aggregate calls now run twice.
Line 3station is legal in the second SELECT list only because it is the grouping key: it has one value for every row in its group.
Line 4Nothing about COUNT or AVG changed between the queries; only the set of rows each call sees changed.
Important notes
Grouping deliberately treats all NULLs in the key as one value, which is the opposite of how NULL behaves in a WHERE comparison.
Some engines allow GROUP BY 1 (by select position) or GROUP BY a SELECT alias; both are shorthands that other engines reject, so name the column when portability matters.
Common mistakes
Leaving an extra bare column in the SELECT list, as in SELECT carrier, weight_kg, SUM(weight_kg) ... GROUP BY carrier: PostgreSQL raises "must appear in the GROUP BY clause", while SQLite and default MySQL print one arbitrary shipment's weight next to a total covering three shipments.
Writing WHERE COUNT(*) > 1 to keep only busy carriers: the query fails because WHERE is evaluated before any group exists, and the group-level test belongs in HAVING.
Assuming every known category shows up in the result: a carrier whose rows were all filtered out, or which has no rows yet, produces no row rather than a 0, so it silently vanishes from a report.
Try it yourself
Change, predict, then run
Create orders(country TEXT, amount REAL) with six rows covering three countries, one of which appears only once. Before running it, predict how many rows a query returning country, COUNT(*) and SUM(amount) grouped by country will produce, then run it and compare.
Open the SQL workspaceCheck your understanding
A tasks table has 1000 rows and its status column contains exactly four distinct values, one of which is NULL. How many rows does SELECT status, COUNT(*) FROM tasks GROUP BY status return?
- 3, because rows with a NULL status cannot be grouped
- 4, one row per distinct status value, with NULL forming its own group
- 1000, one row per input row with the group count repeated on each
- 1, because COUNT(*) always collapses the result to a single row
Show answer
The output row count of a grouped query is the number of distinct key values among the rows that reach the GROUP BY step, and NULL counts as one such value, so four rows come back. Option 0 borrows the rule that NULL = NULL is never true, but GROUP BY makes an explicit exception and gathers all NULL keys into a single group; COUNT(*) collapses to one row only when there is no GROUP BY at all.