The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

The HAVING Clause

Next ►
►

You already know how to form groups and compute aggregates over them. One last piece is missing: filtering the groups. Not "the orders from 2025", which is filtering rows and WHERE already does that, but "the customers who have placed more than one order", "the categories billing more than €150", "the products that have sold more than 8 units". Those conditions can't be evaluated by looking at a single row: the group has to have been formed and its aggregate computed before you can decide.

That's what HAVING exists for. Its definition fits in one line —WHERE filters rows, HAVING filters groups— and everything else follows from the logical-order diagram you completed yesterday. In this lesson you'll see why HAVING can use aggregates and WHERE can't, why WHERE is nonetheless almost always preferable, how the two combine in the same query, and you'll close the thread 03-03 left open with the definitive table of the three places you can filter in SQL.

And with that, module 4 closes.

Contents

  1. HAVING in the logical execution order
  2. The first HAVING: customers with more than one order
  3. Why HAVING can use aggregates and WHERE can't
  4. The same question solved both ways
  5. Why WHERE is preferable: filtering before grouping
  6. WHERE and HAVING in the same query
  7. HAVING with no GROUP BY
  8. Several conditions in HAVING
  9. HAVING over an aggregate that isn't in the SELECT
  10. GreenStore business cases
  11. The three places you filter: ON, WHERE and HAVING
  12. Common Mistakes and Tips
  13. Exercises
  14. Module conclusion

  1. HAVING in the logical execution order

Bring back 04-05's diagram and look at step 4:

flowchart LR
    A["1 · FROM / JOIN<br/>where the rows come from"] --> B["2 · WHERE<br/>filters ROWS<br/>(no aggregates)"]
    B --> C["3 · GROUP BY<br/>forms the GROUPS"]
    C --> D["4 · HAVING<br/>filters GROUPS<br/>(aggregates allowed)"]
    D --> E["5 · SELECT<br/>projects · aliases are born"]
    E --> F["5b · DISTINCT"]
    F --> G["6 · ORDER BY"]
    G --> H["7 · LIMIT / OFFSET"]

HAVING sits after GROUP BY and before SELECT. All five of the clause's properties come out of that single position:

Property Why
It can use aggregate functions By the time it runs, the groups are already formed and their aggregates computed
It can use the GROUP BY columns They're the ones defining each group and they have a single value per group
It can't use bare columns A non-grouped column has no unique value within the group. Same error as in the SELECT
It doesn't see the SELECT's aliases The SELECT is step 5, later on. You have to repeat the whole aggregate
It discards whole groups, not rows If a group doesn't pass the condition, it disappears with all its rows

Syntactically it always goes between GROUP BY and ORDER BY:

SELECT columns, aggregates
FROM tables
WHERE   condition_on_rows
GROUP BY columns
HAVING  condition_on_groups
ORDER BY …
LIMIT …

  1. The first HAVING: customers with more than one order

The classic retention question: which customers have come back?

SELECT c.id,
       c.name || ' ' || c.last_name AS customer,
       c.country,
       COUNT(*)            AS orders,
       MIN(o.order_date)   AS first_order,
       MAX(o.order_date)   AS last_order
FROM orders    AS o
JOIN customers AS c ON o.customer_id = c.id
GROUP BY c.id, c.name, c.last_name, c.country
HAVING COUNT(*) > 1
ORDER BY orders DESC, c.id;
id customer country orders first_order last_order
1 Lucía Martínez Soler Spain 3 2025-03-04 2025-12-02
2 Carlos Ferrer Ibáñez Spain 2 2025-03-12 2025-09-09
4 Javier Ortega Ruiz Spain 2 2025-04-19 2025-12-19
5 Ana Belmonte Roca Spain 2 2025-05-23 2026-01-27
6 Pau Llorens Vidal Spain 2 2025-06-11 2026-02-09
7 Sofia Moreira Costa Portugal 2 2025-06-28 2026-01-13
9 Camille Dubois France 2 2025-08-03 2026-02-21

7 customers out of 15 have come back. Without the HAVING, the query would return 12 rows (the 12 buyers); with it, only the groups whose COUNT(*) exceeds 1 remain. Five whole groups have been discarded: Marta, Tiago, Julien, Elena and Diego, each with their single order.

Notice that this condition is impossible to write in the WHERE. When the WHERE is evaluated, the query is looking at Lucía's order 1 and has no way of knowing that orders 5 and 15 will exist: the groups haven't been formed yet.

  1. Why HAVING can use aggregates and WHERE can't

Let's try it, to see the error:

-- ⚠️ INCORRECT
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE COUNT(*) > 1
GROUP BY customer_id;
ERROR:  aggregate functions are not allowed in WHERE
LINE 3: WHERE COUNT(*) > 1
              ^

The message is blunt and the diagram explains why. WHERE runs at step 2, when the engine is examining one row at a time and no group exists yet. COUNT(*) would mean nothing there: a count of what?

HAVING runs at step 4, with the groups already built and their aggregates already computed. It can ask about COUNT(*), SUM(...), AVG(...) or anything else, because they're values that already exist.

flowchart TD
    A["20 rows of orders"] --> B["2 · WHERE<br/>looks at one row at a time<br/>❌ no groups: COUNT(*) doesn't exist"]
    B --> C["3 · GROUP BY customer_id<br/>12 groups"]
    C --> D["COUNT(*) is computed<br/>for each group"]
    D --> E["4 · HAVING COUNT(*) > 1<br/>✅ the aggregates already exist<br/>→ 7 groups"]

And the mirror image: HAVING can't use a column that's neither grouped nor aggregated, exactly like the SELECT:

-- ⚠️ INCORRECT
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING status = 'delivered';
ERROR:  column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function
LINE 4: HAVING status = 'delivered';
               ^

It's 04-05's golden rule, applied to HAVING. Customer 5's group contains one cancelled order and one paid one: what is "the status" of that group? There's no answer. If what you wanted was to filter by status, that's a row filter and it goes in the WHERE.

And a third restriction, which you already saw in 04-05: HAVING doesn't accept SELECT aliases in PostgreSQL.

-- ⚠️ INCORRECT in PostgreSQL (it works in MySQL and SQLite)
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING orders > 1;
ERROR:  column "orders" does not exist
LINE 4: HAVING orders > 1;
               ^

You have to repeat the aggregate: HAVING COUNT(*) > 1. It's redundant and it's what the standard demands, because aliases are born at step 5.

  1. The same question solved both ways

When the condition falls on a GROUP BY column, it can indeed be written in either place, and the result is identical. It's the best experiment for understanding the difference.

The question: "how many orders were paid by card?"

Version A — filtering rows with WHERE:

-- ✅ CORRECT and preferable
SELECT payment_method,
       COUNT(*)           AS orders,
       SUM(shipping_cost) AS shipping
FROM orders
WHERE payment_method = 'card'
GROUP BY payment_method;
payment_method orders shipping
card 11 52.10

Version B — filtering groups with HAVING:

-- ✅ CORRECT but worse
SELECT payment_method,
       COUNT(*)           AS orders,
       SUM(shipping_cost) AS shipping
FROM orders
GROUP BY payment_method
HAVING payment_method = 'card';
payment_method orders shipping
card 11 52.10

An identical result. Both are valid and payment_method is legal in the HAVING because it's in the GROUP BY. But they don't do the same work:

flowchart TD
    subgraph A["Version A · WHERE"]
      A1["20 rows"] --> A2["WHERE payment_method='card'<br/>→ 11 rows"]
      A2 --> A3["GROUP BY over 11 rows<br/>→ 1 group"]
      A3 --> A4["✅ 1 row"]
    end
    subgraph B["Version B · HAVING"]
      B1["20 rows"] --> B2["GROUP BY over all 20<br/>→ 4 groups"]
      B2 --> B3["The 4 groups are aggregated"]
      B3 --> B4["HAVING discards 3<br/>→ 1 group"]
      B4 --> B5["✅ 1 row"]
    end

Version A groups 11 rows and forms 1 group. Version B groups 20 rows, forms 4 groups, computes four sums and then throws three in the bin. With 20 orders the difference is imperceptible; with twenty million, it's the difference between an instant query and one that makes the server sweat.

The rule: if the condition can be evaluated by looking at a single row, it goes in the WHERE. Save HAVING for what requires an aggregate to have been computed.

  1. Why WHERE is preferable: filtering before grouping

The principle was stated in 02-03 and here it reaches its clearest expression: filtering early is filtering cheaply. Every row discarded by the WHERE is a row that doesn't have to be sorted, put into a hash table, or aggregated.

Aspect WHERE (before grouping) HAVING (after grouping)
Rows reaching the GROUP BY Only those passing the filter All of them
Groups that get built Only the necessary ones All of them, then discarded
Aggregates that get computed Only the ones to be shown All of them, including the ones to be thrown away
Can it use an index? Yes No: it acts on computed results
Can it use aggregates? No Yes

That index point is the most important one on large tables. A WHERE order_date >= '2025-01-01' can be resolved with an index on order_date, reading only the relevant rows. A HAVING never can: it operates on values that didn't exist until the query computed them.

Seeing it requires EXPLAIN, the tool for comparing execution plans, and that's module 8. But you already have the intuition: in the previous section's version A, PostgreSQL can discard 9 of the 20 rows before touching the GROUP BY; in version B, it can discard none.

Practical tip: when you write a query with GROUP BY, go over each HAVING condition and ask yourself: "does this depend on more than one row?". If the answer is no, move it to the WHERE. It's the cheapest optimisation there is: it needs no indexes, no configuration and no understanding of the planner.

  1. WHERE and HAVING in the same query

The usual case isn't choosing between one and the other: it's using both, each for its own job. WHERE bounds the universe of rows, HAVING selects which groups deserve to appear.

The question: "of the 2025 sales, which customers spent more than €50?"

SELECT c.id,
       c.name || ' ' || c.last_name AS customer,
       c.country,
       COUNT(DISTINCT o.id) AS orders_2025,
       COUNT(*)             AS lines_,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS total_2025
FROM order_lines AS ol
JOIN orders    AS o ON ol.order_id   = o.id
JOIN customers AS c ON o.customer_id = c.id
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2026-01-01'
GROUP BY c.id, c.name, c.last_name, c.country
HAVING SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) > 50
ORDER BY total_2025 DESC, c.id;
id customer country orders_2025 lines_ total_2025
1 Lucía Martínez Soler Spain 3 9 107.60
10 Julien Moreau France 1 3 66.90
7 Sofia Moreira Costa Portugal 1 3 64.88
4 Javier Ortega Ruiz Spain 2 4 62.93
2 Carlos Ferrer Ibáñez Spain 2 4 59.46

5 rows. The division of responsibilities is perfectly clear:

Clause Condition What it discards
WHERE order_date in 2025 The 7 lines of the four 2026 orders. Evaluated row by row
GROUP BY By customer Forms 12 groups from the 40 surviving lines
HAVING Sum > €50 Discards 7 whole groups whose total doesn't reach €50

Without the HAVING the result would have 12 rows; without the WHERE, the totals would include 2026 and Sofia would go from €64.88 to €111.88, even changing the ranking's order. Both filters are necessary and neither can substitute for the other.

And notice a writing detail: the amount expression is repeated in the SELECT and in the HAVING. It's compulsory (the alias total_2025 doesn't exist yet) and it's ugly. Module 10's CTEs will solve it for good.

  1. HAVING with no GROUP BY

HAVING can appear without a GROUP BY. When that happens, SQL treats the whole table as a single group, exactly like an aggregate with no GROUP BY (04-04, section 8). The result is a query that returns one row or none.

SELECT COUNT(*)             AS products,
       ROUND(AVG(price), 2) AS avg_price
FROM products
HAVING COUNT(*) > 5;
products avg_price
20 9.04

Since the catalogue has 20 products and 20 > 5, the single group survives and its row comes out. Now with a condition that isn't met:

SELECT COUNT(*)             AS products,
       ROUND(AVG(price), 2) AS avg_price
FROM products
HAVING COUNT(*) > 100;
(0 rows)

Zero rows. It's the only way an aggregate query with no GROUP BY can return none, and that's why it's disconcerting: without the HAVING, SELECT COUNT(*) FROM products WHERE 1 = 0 would return one row with a 0.

What's it for? Almost nothing, day to day. Its only reasonable use is as a guard on a check: "give me the summary only if there's enough data", or "alert me only if the total exceeds a threshold". Outside that, a HAVING with no GROUP BY is usually a badly written WHERE:

-- ⚠️ You probably wanted this instead
SELECT COUNT(*) FROM products WHERE price > 5;

Practical rule: if you write HAVING and there's no GROUP BY in the query, stop and check that's what you meant. Nine times out of ten, it wasn't.

  1. Several conditions in HAVING

HAVING accepts compound conditions with AND, OR, NOT and parentheses, with the same precedence and the same traps as 02-03's WHERE. AND is evaluated before OR, and mixing them without parentheses produces wrong results with no error at all.

The question: "categories with at least 10 sales lines and more than €150 billed".

SELECT cat.id,
       cat.name AS category,
       COUNT(*)         AS lines_,
       SUM(ol.quantity) AS units,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN products   AS p   ON ol.product_id = p.id
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING COUNT(*) >= 10
   AND SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) > 150
ORDER BY revenue DESC, cat.id;
id category lines_ units revenue
1 Food 16 49 256.27
4 Drinks 11 29 195.28
2 Natural cosmetics 10 16 156.32

3 rows out of 5. Sustainable home has been discarded (7 lines, doesn't reach 10) and so has Personal hygiene (3 lines and €31.50, failing both conditions).

And now the version with OR, which answers a different question:

HAVING COUNT(*) >= 10
    OR SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) > 150

would give the same 3 rows over this data set —all three satisfy both conditions— which is a dangerous coincidence: if tomorrow a category reached 12 lines with €90 of revenue, AND would exclude it and OR would include it. Two queries returning the same thing today doesn't mean they're equivalent.

A reminder from 02-03: whenever AND and OR live together in a HAVING, use parentheses. The precedence is the same and so is the damage.

  1. HAVING over an aggregate that isn't in the SELECT

It's perfectly legal to filter by an aggregate you don't display. It's also one of the things that most confuses whoever reads the query later.

SELECT cat.id,
       cat.name AS category,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN products   AS p   ON ol.product_id = p.id
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING SUM(ol.quantity) > 25
ORDER BY revenue DESC, cat.id;
id category revenue
1 Food 256.27
4 Drinks 195.28

2 rows, and whoever reads this result can't tell why. Neither Natural cosmetics (€156.32) nor Sustainable home appears, despite billing more than plenty of things. The reason is hidden in the HAVING: only Food (49 units) and Drinks (29 units) exceed 25 units sold, and that column isn't displayed.

It's legal, it works and sometimes it's what you want (an executive report doesn't have to show its cut-off criteria). But as a matter of hygiene:

If you filter by an aggregate, display it. Adding SUM(ol.quantity) AS units to the SELECT costs one line and turns a mysterious result into a self-explanatory one. Your future self, six months from now, will thank you.

  1. GreenStore business cases

Five real questions, answered.

10.1. Categories with more than three products in the catalogue

SELECT cat.id,
       cat.name AS category,
       COUNT(*)               AS products,
       ROUND(AVG(p.price), 2) AS avg_price
FROM products   AS p
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING COUNT(*) > 3
ORDER BY products DESC, cat.id;
id category products avg_price
1 Food 5 6.18
2 Natural cosmetics 4 11.54
3 Sustainable home 4 10.09
4 Drinks 4 8.90

4 categories out of 6. Personal hygiene (2 products) and Supplements (1) are left out. It's the diagnosis of a catalogue with two clearly underdeveloped areas.

10.2. Products that have sold more than 8 units

SELECT p.id,
       p.name AS product,
       SUM(ol.quantity) AS units,
       COUNT(*)         AS times_sold,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN products AS p ON ol.product_id = p.id
GROUP BY p.id, p.name
HAVING SUM(ol.quantity) > 8
ORDER BY units DESC, p.id;
id product units times_sold revenue
2 Organic brown rice 1 kg 14 4 54.60
5 Organic crushed tomato 400 g 14 2 23.79
16 Ginger kombucha 750 ml 12 3 56.43
1 Extra virgin olive oil 500 ml 9 5 109.53
14 Organic chamomile tea 20 bags 9 3 29.25
18 Bamboo toothbrush 9 3 31.50

6 products of the 17 that have ever been sold. They're the fastest-moving ones, and the purchasing team should keep an eye on their stock as a priority.

10.3. Categories whose revenue exceeds €150

SELECT cat.id,
       cat.name AS category,
       COUNT(*) AS lines_,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN products   AS p   ON ol.product_id = p.id
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) > 150
ORDER BY revenue DESC, cat.id;
id category lines_ revenue
1 Food 16 256.27
4 Drinks 11 195.28
2 Natural cosmetics 10 156.32

3 categories account for €607.87 of the €727.95 billed, 83.5 %. It's the Pareto rule showing up in a data set of twenty orders.

10.4. Sales reps with more than three orders handled

SELECT e.id,
       e.name || ' ' || e.last_name AS sales_rep,
       e.job_title,
       COUNT(*)            AS orders,
       SUM(o.shipping_cost) AS shipping
FROM orders    AS o
JOIN employees AS e ON o.employee_id = e.id
GROUP BY e.id, e.name, e.last_name, e.job_title
HAVING COUNT(*) > 3
ORDER BY orders DESC, e.id;
id sales_rep job_title orders shipping
4 Óscar Peris Blasco Sales rep 4 22.40
5 Laia Puig Sanchis Sales rep 4 32.30

2 rows. It's worth recalling 04-05's warning here: this query uses an INNER JOIN, so the 10 web-channel orders aren't there and neither are the five employees with no orders. For this particular report it makes no difference —we're asking about who handles more than three, and someone handling zero isn't a candidate— but if the question were "how the sales workload is distributed", half the company would be missing.

10.5. Customers whose average order value beats the overall average

This is the one that can't be solved yet, and it's worth understanding exactly why.

Let's start with what we do know how to compute. Each customer's average order value:

SELECT c.id,
       c.name || ' ' || c.last_name AS customer,
       COUNT(DISTINCT o.id) AS orders,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount))
             / COUNT(DISTINCT o.id), 2) AS avg_order_value
FROM order_lines AS ol
JOIN orders    AS o ON ol.order_id   = o.id
JOIN customers AS c ON o.customer_id = c.id
GROUP BY c.id, c.name, c.last_name
ORDER BY avg_order_value DESC, c.id;
id customer orders avg_order_value
10 Julien Moreau 1 66.90
7 Sofia Moreira Costa 2 55.94
8 Tiago Almeida Nunes 1 44.60
1 Lucía Martínez Soler 3 35.87
9 Camille Dubois 2 35.44
12 Diego Ramos Herrera 1 31.70
4 Javier Ortega Ruiz 2 31.47
11 Elena Navarro Puig 1 30.30
2 Carlos Ferrer Ibáñez 2 29.73
3 Marta Sanchis Gil 1 29.53
6 Pau Llorens Vidal 2 28.67
5 Ana Belmonte Roca 2 27.43

And we also know how to compute the overall average of the 20 orders, in another query: €36.40.

What we can't do is write HAVING ... > (the overall average) inside the same query, because that value is itself an aggregate computed over another set of rows. HAVING can compare a group's aggregate with a constant (> 50) or with another aggregate from the same group (SUM(a) > SUM(b)), but not with a global aggregate.

-- ⚠️ This is NOT valid SQL
HAVING AVG(...) > AVG(...)_over_the_whole_table

The solution is a subquery that computes the overall average and injects it into the HAVING, and that's lesson 07-01. There you'll write something of this shape:

-- A preview of 07-01: don't write it yet
HAVING SUM(...) / COUNT(DISTINCT o.id) > (SELECT AVG(...) FROM ...)

When you get there, you'll know the answer is three customers —Julien Moreau, Sofia Moreira Costa and Tiago Almeida Nunes—, the only ones beating the €36.40 overall average order value. For now, the honest way to solve it is with two queries and comparing by hand; knowing when a question needs a tool you don't have yet is as important as knowing how to use the ones you do.

  1. The three places you filter: ON, WHERE and HAVING

Here the thread 03-03 left open is closed. SQL has three places to put a condition, and each acts at a different moment of the logical order:

flowchart LR
    A["FROM<br/>tables"] --> B["ON<br/>① conditions the<br/>MATCHING"]
    B --> C["WHERE<br/>② filters ROWS<br/>already matched"]
    C --> D["GROUP BY<br/>forms groups"]
    D --> E["HAVING<br/>③ filters GROUPS"]
    E --> F["SELECT"]
ON WHERE HAVING
When it acts Step 1, inside the FROM Step 2 Step 4
What it acts on Candidate pairs of rows Rows from the FROM's result Groups
Can it use aggregates? No No Yes
Can it use SELECT aliases? No No No (in PostgreSQL)
Can it refer to non-grouped columns? Yes Yes No
Effect in an INNER JOIN Equivalent to putting it in WHERE Equivalent to putting it in ON —
Effect in a LEFT JOIN Keeps the partnerless left rows Degrades the LEFT into an INNER —
Typical question "Every X with their Y meeting Z" "Only the rows meeting Z" "Only the groups meeting Z"

The decision tree

flowchart TD
    Q{"What do I want to filter?"}
    Q -->|"Which right-table rows<br/>get matched in an OUTER JOIN"| ON["ON<br/>(03-03)"]
    Q -->|"Which individual rows<br/>go into the calculation"| W["WHERE<br/>(02-03)"]
    Q -->|"Which groups appear<br/>in the result"| H["HAVING<br/>(04-06)"]
    W --> N["If the condition<br/>needs an aggregate,<br/>it does NOT fit here → HAVING"]
    H --> M["If the condition can be<br/>evaluated row by row,<br/>it should NOT be here → WHERE"]

And the final check with all three at once, in a single GreenStore query:

SELECT c.id,
       c.name || ' ' || c.last_name AS customer,
       COUNT(o.id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.id                 -- ① ON: only matches the delivered ones
      AND o.status      = 'delivered'
WHERE c.country = 'Spain'                      -- ② WHERE: only Spanish customers
GROUP BY c.id, c.name, c.last_name
HAVING COUNT(o.id) >= 2                        -- ③ HAVING: only those with 2 or more
ORDER BY delivered_orders DESC, c.id;
id customer delivered_orders
1 Lucía Martínez Soler 3
2 Carlos Ferrer Ibáñez 2

2 rows, and the three clauses have each done a different, irreplaceable job:

  • The ON restricted the matching to delivered orders, without removing the customers who have none (if that condition had gone into the WHERE, the LEFT JOIN would have degraded into an INNER, exactly as 03-03 demonstrated).
  • The WHERE filtered on a column of the left table, which is safe and degrades nothing: the 11 Spanish customers remain.
  • The HAVING discarded the 9 Spanish customers with fewer than two delivered orders — among them Ana Belmonte Roca, who has two orders but none delivered, and Núria, Hugo and Inés, who appeared with an honest 0 thanks to the LEFT JOIN and to COUNT(o.id).

All the course's filtering logic, in eleven lines.

Common Mistakes and Tips

  • Putting an aggregate in the WHERE. ERROR: aggregate functions are not allowed in WHERE. It goes in HAVING.
  • Putting a condition in the HAVING that can be evaluated row by row. It works, but it groups rows that were going to be thrown away. Move it to the WHERE.
  • Using a column in HAVING that isn't in the GROUP BY. column ... must appear in the GROUP BY clause. If it's a row filter, it goes in the WHERE.
  • Using a SELECT alias in the HAVING. column "..." does not exist in PostgreSQL, even though MySQL and SQLite allow it. Repeat the aggregate.
  • Writing HAVING with no GROUP BY by mistake. It treats the whole table as one group and returns one row or none. You almost always wanted a WHERE.
  • Mixing AND and OR in the HAVING without parentheses. The same precedence and the same silent damage as in the WHERE (02-03).
  • Filtering by an aggregate you don't display. It's legal and it leaves an incomprehensible result. Add it to the SELECT.
  • Forgetting that an INNER JOIN in the query has already discarded groups. HAVING filters what reaches it; if the JOIN lost rows earlier, the report was already incomplete (04-05, section 10).
  • Trying to compare a group's aggregate with a global aggregate. It needs a subquery: 07-01.
  • Tip: write the query in layers. First the FROM with its JOINs and count rows; then the WHERE; then the GROUP BY, checking the number of groups; and only at the end the HAVING. Debugging an aggregate query written in one go is torture.
  • Tip: check how many groups the HAVING discards. Run the query without it and compare: 12 customers against 7, 5 categories against 3. That delta confirms the condition does what you think.
  • Tip: read the business question looking for the subject. "Customers who have placed more than one order" → the subject is the customer and the condition is about their set of orders: GROUP BY customer + HAVING. "Orders over €50" → the subject is the order: it may well be a WHERE.

Exercises

Exercise 1

Purchasing wants to identify suppliers with a substantial catalogue. Write a query that returns, for each supplier with at least 4 products in the catalogue: their name, their country, the number of products, the average price (two decimals) and the price of the most expensive product.

Sort by number of products descending. Then answer: which supplier is left out and why?

Exercise 2

Management wants to know in which months of 2025 revenue exceeded €60. Write a single query using WHERE and HAVING, showing the month (YYYY-MM), the number of distinct orders, the number of lines and the revenue.

Then state, for each of the three filtering clauses, which condition belongs to it and why it couldn't go in the others.

Exercise 3

A colleague hands you this query with the comment "I want the categories with more than 20 units sold, but I'm missing categories and I'm not sure the number is right":

-- ⚠️ INCORRECT
SELECT cat.name AS category,
       SUM(ol.quantity)     AS units,
       SUM(o.shipping_cost) AS shipping
FROM order_lines AS ol
JOIN orders     AS o   ON ol.order_id   = o.id
JOIN products   AS p   ON ol.product_id = p.id
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.name
HAVING SUM(ol.quantity) > 20;
  1. Which categories does it return and which categories are "missing"? Is it the HAVING's fault?
  2. The shipping column is wrong. Explain why and work out what it's really worth for Food against what it should be worth.
  3. Rewrite the query fixing what can be fixed today and state which part needs module 7.

Solutions

Solution 1

SELECT s.id,
       s.name AS supplier,
       s.country,
       COUNT(*)               AS products,
       ROUND(AVG(p.price), 2) AS avg_price,
       MAX(p.price)           AS priciest
FROM products  AS p
JOIN suppliers AS s ON p.supplier_id = s.id
GROUP BY s.id, s.name, s.country
HAVING COUNT(*) >= 4
ORDER BY products DESC, s.id;
id supplier country products avg_price priciest
1 Huerta del Turia Spain 5 5.74 12.50
3 Verde Atlántico Portugal 4 12.91 22.00
4 Maison Nature France 4 9.93 18.90
5 EcoNordic Supplies Germany 4 11.21 16.40

4 rows. Left out is BioSierra Ibérica, which supplies only 3 products (the honey, the spelt pasta and the chamomile tea) and doesn't reach the threshold of 4. Check: 5 + 4 + 4 + 4 + 3 = 20 products.

Two observations about these average prices, which now really are catalogue prices: they're different from the ones in solution 2 of 04-05 (€6.73 for Huerta del Turia instead of €5.74). The difference is that here we start from products and each reference counts once; there we started from a LEFT JOIN with order_lines and each reference counted as many times as it had been sold. Both averages are correct and answer different questions: "average catalogue price" versus "average price of what gets sold". Checking which table a query starts from before interpreting its average is a habit that avoids a lot of grief.

And notice that EcoNordic Supplies appears despite being inactive: the query doesn't filter by s.active. If the question were "operational suppliers with a substantial catalogue", you'd have to add WHERE s.active — a row filter, not a group one.

Solution 2

SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS month,
       COUNT(DISTINCT o.id)             AS orders,
       COUNT(*)                         AS lines_,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN orders AS o ON ol.order_id = o.id
WHERE o.order_date >= DATE '2025-01-01'
  AND o.order_date <  DATE '2026-01-01'
GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
HAVING SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) > 60
ORDER BY revenue DESC, month;
month orders lines_ revenue
2025-10 2 5 97.20
2025-06 2 5 95.48
2025-03 2 5 68.80
2025-12 2 5 64.58
2025-04 2 5 61.28

5 months out of 2025's 10 exceeded €60. And an interesting pattern appears: all five are months with 2 orders and 5 lines. At this volume, billing well in a month simply means two orders landing instead of one.

Division of responsibilities:

Clause Condition Why it goes here
ON ol.order_id = o.id It's the matching, not a filter. Without it there's no query
WHERE 2025 dates Evaluated row by row: each line knows its order's date. Discarding it early avoids grouping 2026's 7 lines. It can also use an index on order_date
HAVING Revenue > €60 It needs the SUM of the whole month. No individual line can decide whether its month exceeds €60

The WHERE's condition couldn't go in the HAVING: o.order_date isn't in the GROUP BY (we group by month, not by day), so it would give the error must appear in the GROUP BY clause. And the HAVING's couldn't go in the WHERE: aggregate functions are not allowed in WHERE. Each one fits only where it is.

Solution 3

1. What it returns and what's missing.

-- ⚠️ INCORRECT
category units shipping
Food 49 86.75
Drinks 29 90.10

2 rows. The "missing" categories are:

  • Natural cosmetics (16 units), Sustainable home (10) and Personal hygiene (9): they aren't missing by mistake, that's the HAVING doing its job. None of them exceeds 20 units. Correct.
  • Supplements (0 units): this one is missing for a different reason. The HAVING didn't discard it, the INNER JOIN discarded it much earlier, at step 1. It's 04-05 section 10's problem. That said: since its condition would be 0 > 20, it wouldn't have appeared with a LEFT JOIN either. The result is the same, but for different reasons, and that distinction matters — with the threshold at > 20 it makes no difference, with the threshold at >= 0 it would make all the difference in the world.

2. Why shipping is wrong. shipping_cost lives in orders, and after the JOIN with order_lines each order appears as many times as it has lines. It's 04-04 section 11's mistake, now inside a GROUP BY.

For Food, the query gives €86.75; for Drinks, €90.10. Added across every category they'd give €278.70, the same inflated number from 04-04 section 11, against the real €118.25.

And what should it give? The question isn't even well posed: shipping costs can't be split by category, because an order with products from three categories pays one shipping charge, not three. Order 1's €4.95 aren't "€4.95 of Food": they're €4.95 of the order, and order 1 carries products from Food and from Drinks.

That's the deepest point of the exercise. The number is wrong, but the real problem is that the metric doesn't exist at the level of granularity being asked for. It's exactly the kind of question you should hand back to whoever asked it.

3. The corrected version:

-- ✅ CORRECT for what can actually be answered today
SELECT cat.id,
       cat.name AS category,
       COUNT(*)         AS lines_,
       SUM(ol.quantity) AS units,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM order_lines AS ol
JOIN products   AS p   ON ol.product_id = p.id
JOIN categories AS cat ON p.category_id = cat.id
GROUP BY cat.id, cat.name
HAVING SUM(ol.quantity) > 20
ORDER BY units DESC, cat.id;
id category lines_ units revenue
1 Food 16 49 256.27
4 Drinks 11 29 195.28

Changes applied:

Change Reason
The JOIN with orders is removed It's no longer needed: without shipping_cost, order_lines and products are enough
SUM(o.shipping_cost) is removed The metric doesn't exist at category level
revenue is added, which does live on the line It's the correct metric at this granularity
cat.id is added to the GROUP BY and the ORDER BY A deterministic tie-break and grouping by the PK

What needs module 7. If the question were "how much of the shipping is attributable to each category, prorated by each category's weight in the order's amount?", that's a legitimate metric but it requires computing each order's total first, then each line's weight within that total, and then distributing. That "compute an aggregate and use it in another calculation" is precisely what module 7's subqueries and module 10's CTEs solve.

Module conclusion

HAVING closes SQL's filtering logic:

  • WHERE filters rows, HAVING filters groups. Everything else follows from its position in the logical order: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
  • HAVING can use aggregates and WHERE can't, because when the WHERE runs the groups don't exist yet. And HAVING can't use non-grouped columns, by the same golden rule as the SELECT.
  • When the condition falls on a GROUP BY column, the two spellings give the same result but not the same work: WHERE groups 11 rows, HAVING groups 20 and throws away 3 groups. Filtering before grouping is always preferable, and it's also the only one that can use an index (EXPLAIN, module 8).
  • The normal thing is to use both at once: WHERE bounds the universe (2025) and HAVING picks the groups that matter (more than €50).
  • HAVING with no GROUP BY treats the whole table as one group and returns one row or none. It's almost always a badly written WHERE.
  • The three places you filter are ON (conditions the matching and keeps a LEFT JOIN's left rows), WHERE (filters rows and degrades the LEFT into an INNER if it touches the right table) and HAVING (filters groups). The thread opened in 03-03 is now closed.
  • You've solved GreenStore's real business cases: 7 repeat customers, 4 categories with more than 3 products, 6 products with more than 8 units sold, 3 categories accounting for 83.5 % of revenue. And you know which questions you still can't answer: comparing a group's aggregate with a global aggregate requires subqueries (07-01).

And with that, module 4 closes. Look back at what you've gained in six lessons: you search by text pattern with LIKE, ILIKE and regular expressions, and you know which of them can use an index; you filter by lists and ranges with IN and BETWEEN, and you know SQL's most expensive mistake —NOT IN with a NULL—; you understand three-valued logic and with it five mysteries you'd been dragging since module 2 were all resolved at a stroke; you compute with COUNT, SUM, AVG, MIN and MAX, and you know that they all ignore nulls except COUNT(*); you split the data into groups with GROUP BY combined with JOIN, which is the central pattern of all data analysis; and you filter those groups with HAVING. As a bonus, you've settled the warning module 3 repeated three times: €278.70 against €118.25, and you know exactly why.

Up to here, though, you've only read. The four statements you've used —SELECT, FROM, WHERE, GROUP BY— all belong to the same sublanguage, the DQL 01-01 talked about, and none of them has ever changed a single byte of GreenStore. You could have worked through the whole course on a read-only database and you wouldn't have noticed the difference.

But a shop that can't add a product, record an order, correct a price or cancel a purchase isn't a shop: it's a dead catalogue. In module 5, Data Manipulation, you'll cross to the other side. You'll learn to create tables with CREATE TABLE —with the types, keys and constraints you've spent five modules reading in GreenStore's schema—, to insert rows with INSERT, to modify them with UPDATE, to remove them with DELETE, to solve the classic "insert it if it doesn't exist and update it if it does" with UPSERT, and to change a table's structure in production with ALTER TABLE. The level of responsibility changes too: a badly written SELECT returns a wrong number, but an UPDATE with no WHERE modifies all twenty rows of the table and there's no way to undo it. That WHERE you've spent four modules sharpening stops being a matter of analytical precision and becomes your safety net.

◄ Previous
◄
Next ►
►

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

The Project | About Us | Contribute | Donations | License
© Copyright 2026. All rights reserved