HAVING vs WHERE — Filtering Groups and Filtering Rows

The situation this lesson prepares you for

Somebody asks you for "our significant markets". Or "the customers worth chasing". Or "the countries we can always ship to cheaply".

Every one of those is a question about a group, not about a row. No single order is a significant market. No single delivery makes a country cheap to ship to. The answer only exists once you have piled the rows up and worked something out about each pile — and the piling is the part you can already do.

What you cannot do yet is throw a pile away.

Last lesson you built one number per group and then hit a wall: you could produce the totals and you could not filter them. Ask for only the countries with more than five orders and the database refuses outright. That refusal is not a limitation to work around. It is the database telling you that at the moment your WHERE runs, the number you are asking about does not exist yet.

By the end of this lesson you will be able to filter on a total, say which of the two filters any given condition belongs in, and recognise the mistake that puts it in the wrong one — because that mistake does not produce an error. It produces a report that agrees with itself.

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.

HAVING: the word you were missing

Here is the query you could not write last time.

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

Six countries out of ten. The other four are still in the table exactly where they were — they simply did not clear the bar.

One new word, and notice where it sits: underneath the GROUP BY, not above it. That position is the whole idea, and it is not a style choice. WHERE gets its say before the piles are built. HAVING gets its say after they exist.

Read the condition out loud and you can hear why it could not have gone anywhere else. More than five orders. No single order "has more than five orders" — that is not even a sentence. You can only ask that of a whole country at once.

Two filters, and they are not rivals

It is tempting to think of HAVING as "the advanced one" or "the one for hard conditions". It is neither. It does exactly the same job WHERE does — keep the things you want, throw the rest away. The only difference is what each of them is looking at.

• WHERE is handed one row and asks a question about it.
• HAVING is handed a whole group and asks a question about all of it at once.

HAVING is not stronger. It is later. By the time it runs, there is something bigger to ask about.

The order your clauses take effect

This is the most useful thing in the lesson, and it explains everything else in it.

You write a query in this order:

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

The database runs it in a different one:

1. FROM — fetch the rows.
2. WHERE — throw rows away, one at a time. No piles exist yet.
3. GROUP BY — build the piles.
4. HAVING — throw whole piles away.
5. SELECT — work out the columns you asked for.
6. ORDER BY — sort whatever survived.

You write SELECT first. The database runs it fifth.

Look at where the two filters sit: WHERE is step 2, HAVING is step 4, and the pile-building is in between them. Every other thing in this lesson falls out of that one gap — the keyword, the error, the trap, and the portability problem at the end.

Why the aggregate in a WHERE is refused

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

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

At step 2 there is no count to compare against. There is not even a group. The database is not being fussy — it is being asked for a number that will not be computed for another two steps. DuckDB says the same thing in different words: "Binder Error: WHERE clause cannot contain aggregates!"

Both filters, one query

Most real reports use both, and each does a job the other cannot.

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

Only the expensive deliveries is a question about one order, so it goes in the WHERE. Only the countries with more than two of them is a question about a pile, so it goes in the HAVING. Trace it down the list: seventy-nine orders go in, WHERE knocks out the cheap ones, the piles are built from what survives, then HAVING drops the small piles. Each step only ever sees what the step before it handed over.

That last sentence is the whole lesson, and the next section is what happens when you forget it.

The trap: a report that proves itself right

Here is a question somebody in a warehouse would actually ask.

Which countries can we always ship to cheaply — where nothing has ever cost us more than 50 to send?

The obvious answer is to filter out the expensive deliveries and see who is left. Say the plan through before you type it:

Nothing over 50 — so throw away the expensive deliveries, group by country, and show me the dearest delivery in each. If the dearest one is under 50, every delivery to that country is under 50.

Hold on to that last sentence. You are about to walk straight past it.

SELECT "ShipCountry", max("Freight") AS "Dearest"
FROM "Orders"
WHERE "Freight" <= 50
GROUP BY "ShipCountry"
ORDER BY "ShipCountry";

Nine countries. Every single row shows a dearest delivery under 50. Nothing failed, nothing warned you, and the report agrees with the question you asked. You could swear this is correct.

It is wrong, and it is wrong in the most dangerous way available: it is self-confirming. Of course every row is under 50 — you deleted everything above 50 before you looked.

Read your filter back before you run it

You do not need any data to know that query is wrong. Read the filter aloud.

WHERE "Freight" <= 50 says: throw away the expensive deliveries, then group whatever is left. That is not what you were asked.

This is what you were asked:

Which countries can we always ship to cheaply — where nothing has ever cost us more than 50 to send?

Read those two next to each other. Your query removes the expensive deliveries. The requirement wants the countries that never had one. Those are not the same job. Removing a row is something you do to a table; having none is something that is either true or false about a whole country — and you cannot find out which by deleting the evidence first.

So the thing you needed to filter on was never a delivery. It was a country.

HAVING max("Freight") <= 50 says: this country's dearest delivery is under 50. That is the requirement, word for word.

GROUP BY is in both. The filter is the only thing that moves — and where it lands decides what it is allowed to ask about.

yours                          what you were asked for
-----                          -----------------------
WHERE "Freight" <= 50          GROUP BY "ShipCountry"
GROUP BY "ShipCountry"         HAVING max("Freight") <= 50

Above the grouping, a filter is handed one delivery at a time — which is why it cannot so much as mention max("Freight"); you saw it refuse. Below the grouping, it is handed a whole country at once, and max is exactly what it is there for.

Now go back to what you said before you typed anything: "if the dearest one is under 50." The dearest one is max("Freight"). You had it. The word is in your English, and it is in your SELECT — and it is nowhere in your filter. That is the whole mistake, and it was visible before a single row was fetched.

The rule worth keeping: your filter should say the requirement back to you. If the filter used reads as a different sentence from the one you were asked in the requirement, it is the wrong filter, and no amount of data will fix that.

And this is what that line cost

"Always cheap" is a claim about every delivery that country has ever had, so a single delivery over 50 makes it false.

SELECT "ShipCountry", "OrderID", "Freight"
FROM "Orders"
WHERE "ShipCountry" = 'Germany'
  AND "Freight" > 50
ORDER BY "Freight" DESC
LIMIT 6;

Order 33 to Germany cost 95.75 — and Germany came back as a country nothing has ever cost more than 50 to reach, with a dearest delivery of 45.15. One row settles it.

And it is not one stray order. Germany has 20 orders over 50, out of 32. The WHERE deleted all twenty before the grouping started, so max("Freight") only ever saw the twelve survivors — and 45.15 is the largest of those. The number was never a lie — it was correct for the query that was written. The query was the thing that was wrong, because it did not ask what the requirement asked.

A note on what is not wrong here. Austria has three orders and all three cost more than 50, so the filter removed every one and Austria never appears in the report at all. That looks like a second bug, and it is not: Austria genuinely is not a country we can always ship to cheaply, so leaving it out is the right answer — reached by accident. For this question, every country the filter drops is a country that fails the test, so a missing row can never be the wrong answer. Only the false positives are the bug.

One country. Not nine.

You already read the fix back to yourself two sections ago. Here it is put where it belongs, and run for real.

SELECT "ShipCountry", max("Freight") AS "Dearest"
FROM "Orders"
GROUP BY "ShipCountry"
HAVING max("Freight") <= 50
ORDER BY "ShipCountry";

One row. The UK, at 45.50 — the only country in this data where no delivery has ever cost more than 50 to send.

Same condition, same number, moved from step 2 to step 4. Every order gets counted first, and only then does anything get thrown away. The question was always "is this country's dearest delivery under 50?" — which you cannot answer without looking at all of them, which means it was never a WHERE question at all.

The wrong answer was not slightly wrong. It was the opposite answer.

And one habit that is not SQL

Step 3 of the SQL Thinking Loop, from the very first lesson: before you run it, try guessing the result. Step 4: run it and compare with what your guess was.

Skip both and a wrong report has nothing to collide with. That is what happened here — the nine countries arrived, nothing failed, and there was no expectation for them to contradict.

Germany has the largest freight bill of any country in this data. Seeing it on a list of places nothing has ever cost more than 50 should have stopped you cold: that cannot be right. When the answer argues with what you know about the business, check the query.

It costs a few seconds and it catches the bugs that do not raise an error — which, as this lesson shows, are the only ones worth worrying about.

The test that tells you which one

Before you pick a clause, ask yourself one thing:

Can I answer this by looking at one row?

• The freight on this order is over 50. One row. → WHERE
• This country has never shipped anything over 50. You cannot answer that without seeing all of them. → HAVING

That is the whole rule, and it is a rule about the question asked in the requirement, not about the syntax.

And do not overcorrect. If a condition can go in the WHERE, put it in the WHERE. Throwing rows away early is cheaper than building piles you are about to discard — the database has less to carry through every later step. The goal of this lesson is not to make the learner frightened of WHERE, but to understand that WHERE and HAVING are two different tools — and to understand when to use one and when to use the other.

Some engines will let you put a non-aggregate condition in the HAVING (HAVING "ShipCountry" <> 'Germany' will run on both PostgreSQL and DuckDB, because the column is in the GROUP BY). It works, it is slower, and it tells the next reader you were not sure which clause you meant. Put row conditions in WHERE.

You can filter on a number the report never shows

HAVING is not limited to what you put in the SELECT. If the question is about the pile, it does not matter whether the answer is on the page.

SELECT "ShipCountry", count(*) AS "OrderCount"
FROM "Orders"
GROUP BY "ShipCountry"
HAVING sum("Freight") > 300
ORDER BY "ShipCountry";

Now look at which country is not here. France has 6 orders and it is here. Venezuela has 6 orders and it is not. Same count, same column, one in and one out — and nothing on the page tells you why.

The reason is a total you cannot see:

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

France's freight bill is 347.85 and Venezuela's is 254.38. Only France clears 300, so Venezuela is out — and it is right to be out. The report is correct. But the number that decided it was never printed, so nobody reading that report can check it. That is the price of filtering on a column you do not show.

This is genuinely useful — "our significant markets" almost always means a revenue threshold that nobody wants as a column. It is also worth a comment in your SQL, because six months later the missing row will look like a bug.

Does HAVING need a GROUP BY?

No. It needs a group — and one always exists.

SELECT count(*) AS "Orders", sum("Freight") AS "TotalFreight"
FROM "Orders"
HAVING sum("Freight") > 3500;

The 3500 is arbitrary — HAVING sum("Freight") > 0 returns the very same row. The number is not the point. The fact that the query runs at all is: with no GROUP BY, the whole table is handed to HAVING as a single group.

Which reframes GROUP BY itself. It does not create groups — it splits the one you always had. Your very first count(*) over a whole table, back in the aggregates lesson, was already an aggregate over one group — the whole table; you simply had no word for it.

You will still write the two together almost every time. HAVING without GROUP BY earns its keep inside a subquery, in a handful of genuinely useful cases — but that is a bit more advanced than this lesson.

One last trap, and it moves between engines

You named that count AS "OrderCount". So surely you can filter on the name.

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

Run that in CloudBeaver and PostgreSQL says:

SQL Error [42703]: ERROR: column "OrderCount" does not exist
  Position: 92

And it is right. Go back to the list: HAVING is step 4 and SELECT is step 5. At the moment the HAVING runs, the name "OrderCount" has not been invented yet. It does not exist for another whole step.

Here is the part that will catch you one day: DuckDB allows it. It is being helpful. So the query runs perfectly on your machine, you push it to a PostgreSQL server, and it falls over there instead — which is a far worse afternoon than it failing in front of you.

Write the aggregate out again in the HAVING and it works everywhere:

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

Use HAVING count(*) > 5, and it stops being a question about which engine you are on.

Worth knowing: ORDER BY is step 6, after SELECT, which is why ORDER BY "OrderCount" works fine on both engines while HAVING "OrderCount" does not. That is not an inconsistency — it is the execution order, doing exactly what you learned about.

See it for yourself

The koans cover every idea above, asked about a different part of the business — the lesson counted orders per country, the koans work in the warehouse over "Products". Copying a query across will not work, which is the point.

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

Ten koans, easiest first: the keyword itself, naming the aggregate a condition is about, both filters in one query, filtering on a total the report never shows, the trap in warehouse form (six suppliers or four?), predicting how far wrong the wrong clause is, a group vanishing because WHERE ran first, the comparison operator, the alias problem, and finally a whole query written from scratch.

What goes wrong

The mistake this lesson prevents is not a syntax error. Syntax errors are cheap — you get one, you fix it, thirty seconds gone.

This one ships. WHERE "Freight" <= 50 runs clean, returns nine tidy rows, and every row on it satisfies the condition you asked about. There is nothing to notice. The report confirms itself, which is exactly why it survives being checked: the reviewer reads the question, reads the rows, sees they agree, and approves it.

The damage is that the filter destroyed the evidence the question was about. You asked "does this country have an expensive delivery?" and the WHERE deleted the expensive deliveries before anyone could look. What comes back is a confident, correct-looking answer to a question nobody asked — and somebody in the warehouse starts routing shipments to Germany because a report said Germany was cheap.

One row, or the whole pile. Ask that before you pick the clause.

What's next

Everything you have counted in this lesson — and in the last one — has come out of a single table. You have grouped orders, counted them, totalled their freight and filtered the totals, and you still cannot say what anybody actually bought, because that lives somewhere else.

Next: JOINs, and putting two tables side by side.