GROUP BY — One Number per Customer, per Month, per Anything

The situation this lesson prepares you for

An aggregate function reads many rows and gives back one number. There are five you will use constantly — count, sum, avg, min and max — and they all work the same way: hand them a table, get a single figure. Run count(*) over our Orders table and the answer is 79: every order there is, reduced to one number.

That number is correct — and almost useless. It describes the whole table in one figure, and real questions are almost never about the whole table.

Nobody has ever walked over and asked how many orders there are altogether. They ask how many per country. What we spent per month. Who our biggest customer is — which is one number per customer, twenty-five separate answers, not one.

Every one of those is the same shape — one number per something — and one word gives it away every time. That word is per, and whatever comes after it is what you GROUP BY. Country, month, customer: naming the something is the whole of the clause.

By the end of this lesson you will be able to produce a report with one row per customer, per country, per month or per anything you can compute — and you will be able to spot the two ways such a report quietly starts answering a different question than the one on its heading.

Every query below runs on the Northwind database on PostgreSQL. Type each one into CloudBeaver and compare the result with the table under it. If that is not set up yet, the Learn Data guide gets it running.

The hard way, and why it runs out

You already have a way to do this. Filter to one country and count what is left:

SELECT count(*) AS "OrderCount"
FROM "Orders"
WHERE "ShipCountry" = 'Germany';

That is a real answer to a real question, and there is nothing wrong with it. Asking about one country, once, is exactly what WHERE is for.

The trouble starts when somebody asks for a report — one row per country, all ten of them. With only what you have so far, the way to build that is to run the same query ten times, changing one word each time: Germany, then France, then Mexico, then Sweden, on down the list — and copy the ten answers into a table by hand.

Stop and look at what that actually is. Ten queries that differ by a single word, to produce ten numbers the database could count in one pass. It already knows every country in that column. You would be doing by hand, over and over, the one kind of work a database is built to do for you — and doing it again from the top every time the data changes.

GROUP BY: say what a group is

SELECT "ShipCountry", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCountry"
ORDER BY "ShipCountry";

One new line did that. GROUP BY "ShipCountry" says: make one pile for every different country you find in that column, then run the aggregate once inside each pile.

Two things are worth noticing before you move on.

No country is named anywhere in that query. The database found them. Add a Polish customer tomorrow and the eleventh row appears on its own.

Germany still says 32 — the same number you counted by hand. count(*) did not change its meaning; it just ran ten times instead of once.

And notice what is there: the ORDER BY. It is not decoration. GROUP BY guarantees you one row per pile. It does not guarantee what order those rows come back in. They arrive alphabetically above only because that last line asks for it — take it away and the engine may hand you the same ten rows in any order it likes, one order today and another tomorrow, with nothing changed and nothing to tell you why. Every grouped query in this lesson carries an ORDER BY for that reason.

What GROUP BY actually does to your rows

WHERE throws rows away and hands you back the survivors — same rows, fewer of them.

GROUP BY throws nothing away. It piles the rows up and hands you back one row per pile. Seventy-nine orders went in, ten rows came out, and every order was counted exactly once, in the right pile.

That has one consequence worth holding on to, because every mistake later in this lesson comes from forgetting it:

The row that says Germany, 32 is not in the Orders table. There is no Germany row to go and look at. It exists because you asked for it.

One pile, as many numbers as you like

A group will answer more than one question at a time, and that is the moment a count turns into a report.

SELECT "ShipCountry", count(*) AS "OrderCount",
       sum("Freight") AS "TotalFreight",
       max("Freight") AS "DearestDelivery"
FROM "Orders"
GROUP BY "ShipCountry"
ORDER BY "TotalFreight" DESC
LIMIT 5;

Three aggregates, all computed inside the same ten piles.

One warning from the aggregates lesson still applies in here, and it is harder to spot now. count(*) counts rows; counting a column counts only the rows where that column has a value. Over the whole table that gap was a single number you could see — 79 against 52. Split across ten piles it hides in every one of them, and no report tells you it is there.

Read down the first column and Germany dominates. Read across the last one and something else appears: Venezuela had six orders and the single most expensive delivery in the whole table, at 98.92 — more than Germany's dearest, at 95.75. Two different questions, visible at once, because both numbers came out of the same pass.

The grouped rows are just rows

The result of a GROUP BY is a table like any other, so everything from the sorting lesson still applies to it — including sorting by a number that did not exist until you invented it.

SELECT "ShipCountry", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCountry"
ORDER BY "OrderCount" DESC, "ShipCountry"
LIMIT 5;

Germany takes 32 of our 79 orders — four in every ten we ship.

And note the second sort key, which is doing real work here: Mexico and Sweden both have eight. Grouped rows tie exactly like ungrouped ones, and a LIMIT sitting above an undecided tie is the same defect you met two lessons ago.

Will it run?

Somebody reads that report and asks a perfectly fair question: which cities are those orders going to? So you add "ShipCity" to the select list and change nothing else.

Before you read on, decide what happens. "ShipCity" is a real column, in the same table as the others, and nothing else was touched. Does asking for one more column break anything?

SELECT "ShipCountry", "ShipCity", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCountry";

SQL Error [42803]: ERROR: column "Orders.ShipCity" must appear in the GROUP BY
clause or be used in an aggregate function
  Position: 23

It did not break. It refused.

Now read the message properly, then read your own query again. It names the column — Orders.ShipCity — and it is pointing straight at a line you did not change. You asked for a report per city and the GROUP BY still says "ShipCountry".

That refusal is one you have met before: it is the same one that stopped you putting a product name beside a max() in the aggregates lesson, down to the SQLSTATE. Germany's pile holds eleven different cities. You asked the database to print one of them, and there is no honest answer to which one, so it stops rather than choose for you.

The engine cannot read your mind and know what you meant. What it can do is tell you the query is not valid as written, and hint that the column you asked for is missing from your GROUP BY. That is most of the fix, handed to you.

So group by the city

The report that was asked for is per city. So say that — group by the city itself, not the country:

SELECT "ShipCity", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCity"
ORDER BY "OrderCount" DESC, "ShipCity"
LIMIT 5;

It is the simplest thing that could work, and it runs. One row per city, biggest first — exactly the report that was asked for.

Now look at what is not in it.

Germany. It was the top row of the last report with 32, and here it is gone — because Germany is not a city. Nothing went wrong and nothing was lost. The 32 are still in there; they are just spread across eleven cities, and the biggest of them is Berlin with 5.

Where Germany's 32 went

The video has no room for this table, and it is the clearest way to see what happened. Ask the same question of Germany alone:

SELECT "ShipCity", count(*) AS "OrderCount"
FROM "Orders"
WHERE "ShipCountry" = 'Germany'
GROUP BY "ShipCity"
ORDER BY "OrderCount" DESC, "ShipCity";

Eleven cities, so eleven rows, and the biggest of them is 5. Nothing was lost — add that column up and you get 32 back. Germany did not drop to 5; it stopped being one row at all.

That is also why México D.F. leads the per-city report on 8 while Germany, with four times as many orders, is nowhere near the top: all eight of Mexico's orders sit in a single city, and Germany's are spread thin. Neither report is wrong. They are answering different questions.

The GROUP BY list is the grain

A column in the GROUP BY does not add detail to a row. It splits the row.

A moment ago we grouped the 79 orders per country and got 10 rows. Group the same 79 per city and they fall into 24 piles instead. A country holds many cities, so the same orders are simply cut finer — nothing was thrown away, and both reports still add up to 79.

The column — or columns — you list after GROUP BY define the grain of your report: what a single row of it actually means. Group by country and one row means a country. Group by city and one row means a city. Nothing else in the query decides that. So there is exactly one test to apply:

Only put a column in the GROUP BY if you want your report broken up by it.

Ask it before you add the column, not after. Ten rows and twenty-four rows are both correct; only one of them is what somebody asked you for.

Group by with multiple keys

Start with one column. That forces the right question — what do I really want this report grouped by? — and most reports are finished there. A second key is for when you genuinely want the extra detail. So here is the same idea with two:

SELECT "ShipCountry", "ShipCity", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCountry", "ShipCity"
ORDER BY "ShipCountry", "ShipCity", "OrderCount" DESC;

…and eighteen more rows, 24 in all.

Read the query before you read the answer: it piles by country, and then by city inside each country. So how many rows should it give you?

Twenty-four — the same 24 the city-alone report gave, with the country now sitting beside each city. That is worth pausing on, because it is not a coincidence and it is not guaranteed.

With two columns the pile is decided by the pair of values, not by either column on its own. Two orders go into the same pile only if they agree on both — same country and same city. So GROUP BY "ShipCountry", "ShipCity" gives you one row for every distinct country-and-city pair found in the table, and here that is 24: Argentina + Buenos Aires is one pile, France + Marseille another, France + Nantes a third.

Add a third column and the same rule applies to triples, and so on. Whatever you list, one row comes back per distinct combination of those values.

Which is why the match with the city-alone report is not something to count on. GROUP BY "ShipCity" piles together every order that shares a city name, no matter which country shipped it. So imagine two of our countries both had a city called Springfield:

• GROUP BY "ShipCity" would see one name, make one pile, and report a single Springfield row with both countries' orders added together.
• GROUP BY "ShipCountry", "ShipCity" would see two different country-and-city pairs, make two piles, and report Springfield twice — once per country.

Here no two of our countries share a city name, so there is nothing for the one-column version to merge and both cuts land on 24. Add that Springfield tomorrow and the two reports stop agreeing — which is exactly when the second key earns its place.

Nothing was thrown away here either. The same 79 orders were simply piled differently.

Per month — and there is no month column

This is the shape you will be asked for most, and it is the one that stops people, because there is no "Month" column anywhere in Orders. There is a date.

So group by something you compute:

SELECT EXTRACT(MONTH FROM "OrderDate") AS "Month",
       sum("Freight") AS "TotalFreight"
FROM "Orders"
WHERE "OrderDate" >= DATE '2024-01-01'
GROUP BY EXTRACT(MONTH FROM "OrderDate")
ORDER BY "Month";

EXTRACT(MONTH FROM "OrderDate") pulls the month number out of each date, and that number becomes the pile. You can group by anything you can work out from a row.

Now the shape is readable: March cost us 350.48 in freight, more than triple January. And June drops back to 112.24 — but the latest order in this database is dated the 12th of June, so June is a partial month. Say so on the report, or somebody reads that last figure as the business falling over.

Notice the expression is written twice — once to show it, once to group by it. PostgreSQL and DuckDB would both let you shorten the second one to GROUP BY "Month", reusing the alias from the select list, and it reads better. It is also not standard SQL: other engines reject it and want the expression repeated. Writing it out is the habit that travels.

The ORDER BY is a different case — sorting is allowed to use a select-list alias in standard SQL, so ORDER BY "Month" above is portable as it stands.

WHERE runs first, and a group can vanish

One more thing, and it decides whether a report is right.

WHERE runs before the grouping. It walks the 79 orders one at a time, before any pile exists, and throws some of them away. Only the survivors are ever grouped.

SELECT "ShipCountry", count(*) AS "OrderCount"
FROM "Orders"
WHERE "Freight" > 50
GROUP BY "ShipCountry"
ORDER BY "OrderCount" DESC, "ShipCountry";

Count the rows: nine. The full report has ten. The UK has left, and nothing on the page mentions it.

It is not a data error. The UK has seven orders and its dearest delivery all year was 45.5, so every one of them was filtered out before the piling started — and a pile with nothing in it is never made.

A group with no surviving rows does not come back as zero. It does not come back. A zero is a fact; a missing row is a silence.

And no, you cannot filter on the count

The obvious next request is "only the countries with more than five orders". Put it in the WHERE and you get:

SELECT "ShipCountry", count(*) AS "OrderCount"
FROM "Orders"
WHERE count(*) > 5
GROUP BY "ShipCountry";

SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE
  Position: 68

Which follows from the running order you now know. At the moment WHERE looks at an order, nothing has been piled up, so there is no count to compare against — there is not even a pile. It is a perfectly good question asked one clause too early, and the clause that answers it is the whole of the next lesson.

So: who is our number-one customer?

We have been carrying that question for four lessons, and we now have the tools to take a proper look at it. One pile per customer, count the orders inside each pile, sort by that count:

SELECT "CustomerID", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "CustomerID"
ORDER BY "OrderCount" DESC, "CustomerID"
LIMIT 5;

Three of them, five orders each. By this measure there is no number one — there are three, and the query is being honest about it.

Now ask the same piles a different question. Instead of counting the orders, add up what they cost us to ship:

SELECT "CustomerID", sum("Freight") AS "TotalFreight"
FROM "Orders"
GROUP BY "CustomerID"
ORDER BY "TotalFreight" DESC
LIMIT 5;

FRANK, on three orders — fewer than the three customers who tied at the top of the other list, and two of those three are not on this one at all.

Both lists are correct. They answer different questions, and the question was hiding inside the word biggest the whole time.

Two honest limits on those tables. The customer codes are all you get for now — real company names live in a second table. And this is what each customer cost us to ship, not what they spent; what they bought is in a second table too. Putting two tables together is the JOINs lesson.

What you can do now

For any table, and any request that contains the word per:

1. Name the group. Which column makes a pile — per customer, per country, per month, per category? That is the whole of the GROUP BY.
2. Choose the numbers. As many aggregates as the report needs, all computed inside each pile.
3. Check the grain. Every extra column in the GROUP BY splits your rows further. If you did not want the report broken up by it, take it out.
4. Filter rows with WHERE — and remember that a group whose rows all get filtered out disappears rather than reporting zero.
5. Sort and cut afterwards, ties included; grouped rows are still rows.

Next lesson: HAVING vs WHERE — because you can build totals now, and you still cannot filter them.

The koans: now you write the queries

Each koan is a tiny fill-in-the-blank exercise: a failing test with a ___ where the answer belongs. Fill it in, run it, watch red turn green — and the next one unlocks.

cd c:/DataPallas/db/datazeus
.\zeus.bat koans learnsql series1 _30

On macOS or Linux, run ./zeus.sh koans learnsql series1 _30 from the same folder. The three arguments are course, series and episode.

Says the lesson isn't there? Your copy of DataZeus predates this lesson — run .\zeus.bat update (or ./zeus.sh update) once to fetch the latest lessons, then run the command again. Koans you have already solved are kept.

The file you edit is:

c:\DataPallas\db\datazeus\tests\src\koans\groovy\datazeus\learnsql\series1\_30\GroupByKoans.groovy

Open it in any editor — Notepad++, VS Code, whatever you have. Each koan has a ___ where the answer belongs. Replace one, save, re-run the command above, and watch that koan go green.