HAVING: filtering groups, and how it differs from WHERE

Step 1 Understand it

Why you need it. "Customers with more than one order", "departments whose average salary is over 55000". You can't test those in WHERE, because the counts and averages don't exist until the rows are grouped. HAVING is the filter that runs after grouping.

Watch it happen

Picture it

flowchart LR
  A["10 orders"] -->|"WHERE status = delivered<br/>filters rows"| B["7 rows"]
  B -->|"GROUP BY customer_id"| C["3 groups<br/>1: 275 · 2: 275 · 3: 60"]
  C -->|"HAVING SUM over 100<br/>filters groups"| D["2 groups<br/>customers 1 and 2"]

The idea

Think of two sieves. WHERE is the first sieve: it removes rows before they are put into groups. HAVING is the second: it removes whole groups after they are counted. A query can use both: WHERE keeps only delivered orders, GROUP BY makes a group per customer, then HAVING keeps customers whose delivered total is at least £100. If a condition is about one row (status, date), put it in WHERE — it is cheaper, because fewer rows get grouped.

The shape of it

SELECT group_column, COUNT(*)
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING group_condition;

Words in lower case are the parts you fill in.

How it works, step by step

1. FROM: all 10 orders.
2. WHERE status = 'delivered': 7 rows are left.
3. GROUP BY customer_id: customer 1 has 4 delivered orders (275), customer 2 has 2 (275), customer 3 has 1 (60).
4. HAVING SUM(amount) >= 100: customer 3's group fails and is removed.
5. SELECT returns customers 1 and 2 with their totals.

Analogy

Picking a football team. WHERE is the trial: players under 16 are sent home before teams are formed. HAVING comes after the teams are formed: any team with fewer than 11 players is dropped from the tournament.

Where the analogy breaks

In football you could also drop a team for one bad player. HAVING can't look at individual rows any more — only at things that describe the whole group, such as counts, sums and the grouping columns.

Worked examples

SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 1
ORDER BY dept_id;

SELECT customer_id, SUM(amount) AS delivered_total
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING SUM(amount) >= 100
ORDER BY customer_id;

Must-know points

• WHERE filters rows, before GROUP BY. HAVING filters groups, after GROUP BY.
• Aggregates can be used in HAVING but not in WHERE.
• A row-level condition belongs in WHERE: it is clearer and faster.
• HAVING can test an aggregate that isn't in SELECT, such as HAVING MAX(salary) > 60000.

Common mistakes

• Writing WHERE COUNT(*) > 1 — an error, because counts don't exist yet when WHERE runs.
• Putting a plain row condition in HAVING (HAVING status = 'delivered'): it either fails or does something unexpected.
• Using a SELECT alias in HAVING (HAVING headcount > 1). Some databases allow it; standard SQL and SQL Server don't. Repeat the aggregate.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL HAVING Clause

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL HAVING

  Mode, free

• Practice

  SQLBolt — Lesson 11: Queries with aggregates (Pt. 2)

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain WHERE versus HAVING using the football-trial analogy, then using the orders table.

   Show answer

   At a football trial, WHERE is the check at the gate: each player is judged on their own (too young? go home) before any teams exist. HAVING comes after the teams are formed: it judges whole teams (fewer than 11 players? out of the tournament).

   With orders: WHERE status = 'delivered' throws out single orders that aren't delivered, before grouping. Then GROUP BY customer_id makes one group per customer, and HAVING SUM(amount) >= 100 drops customers whose delivered total is under £100.

   WHERE can't use SUM or COUNT, because the groups don't exist yet. HAVING can, because it runs after GROUP BY.

2. Writefrom a blank page, then run it

   Write a query for customers who placed at least 2 orders, showing customer_id and the number of orders.

   Show answer

   Group the orders per customer, count them, and keep the groups with 2 or more. That is a test on a group, so it goes in HAVING.

   sqlCopy

   SELECT customer_id, COUNT(*) AS orders
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) >= 2
   ORDER BY customer_id;

   Try it

   3 rows

   customer_id	orders
   1	5
   2	2
   3	2

3. Predictsay it before you run it

   Which dept_ids does SELECT dept_id FROM employees GROUP BY dept_id HAVING AVG(salary) > 55000; return?

   Show answer

   Only department 1.

   The averages are: department 1, (95,000 + 70,000 + 70,000 + 80,000) ÷ 4 = 78,750; department 2, 52,500; department 3, 52,000; and the NULL group (Gus), 38,000. Only 78,750 is over 55,000.

   sqlCopy

   SELECT dept_id, AVG(salary)
   FROM employees
   GROUP BY dept_id
   HAVING AVG(salary) > 55000;

   Try it

   1 row

   dept_id	AVG(salary)
   1	78750

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Customer 3 has one NULL amount. Does HAVING SUM(amount) > 50 keep them? Does HAVING COUNT(amount) = COUNT(*)? Explain.

   Show answer

   HAVING SUM(amount) > 50: yes, customer 3 is kept. SUM skips the NULL, so their total is 60, and 60 > 50.

   HAVING COUNT(amount) = COUNT(*): no, customer 3 is dropped. COUNT(amount) skips the NULL and gives 1, while COUNT(*) counts both rows and gives 2. 1 ≠ 2. This is a handy test for "every order in the group has an amount".

   The query below shows both tests for every customer, as 1 (true) or 0 (false).

   sqlCopy

   SELECT customer_id,
          SUM(amount) AS total,
          COUNT(amount) AS with_amount,
          COUNT(*) AS orders,
          SUM(amount) > 50 AS sum_over_50,
          COUNT(amount) = COUNT(*) AS all_known
   FROM orders
   GROUP BY customer_id
   ORDER BY customer_id;

   Try it

   4 rows

   customer_id	total	with_amount	orders	sum_over_50	all_known
   1	425	5	5	1	1
   2	275	2	2	1	1
   3	60	1	2	1	0
   5	33	1	1	0	1

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

WHERE and HAVING are both filters but run at different stages. WHERE filters individual rows before grouping, so it can't use aggregates. HAVING filters groups after GROUP BY, so it can. If a condition doesn't need an aggregate I put it in WHERE, because filtering early means less data to group.

Follow-up questions

Tick each check when you can do it without notes