The HAVING Clause

You already know WHERE filters rows. But once you GROUP BY and start computing COUNT , SUM , and AVG , you often need to filter on those results — "only categories with more than 5 products". That's exactly what HAVING is for.

Learn The HAVING Clause in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end you'll know precisely when to use WHERE versus HAVING , how SQL executes them in order, and how to combine both in one query for clean, powerful reports.

The same six products from earlier lessons. We'll group them by category and filter the groups.

GROUP BY collapses rows that share a value into a single summary row. Here each category becomes one row with a count and a total. HAVING builds directly on this.

HAVING is the WHERE for groups. It runs after grouping, so it can reference aggregates like COUNT(*) that don't exist before grouping happens.

WHERE is the bouncer checking each person at the door. HAVING is the host who, once people have formed tables , keeps only the tables of 4 or more.

This is the heart of the lesson. SQL evaluates clauses in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY . So WHERE trims rows before grouping, and HAVING trims groups after .

HAVING isn't limited to COUNT . Any aggregate works — here we keep categories whose total stock is at least 200.

Fill in the keyword and threshold to keep categories whose average price is under 15.

Real reports use WHERE , GROUP BY , HAVING , and ORDER BY in one query. You can also combine multiple conditions in HAVING with AND / OR .

Because HAVING runs before SELECT assigns aliases, referencing a SELECT alias in HAVING fails in standard SQL. Repeat the aggregate expression instead — it always works.

These clauses are scrambled. Put them in the order SQL requires them to be written :

1) Which clause can use COUNT(*) > 5 — WHERE or HAVING?

HAVING . Aggregates only exist after GROUP BY, and WHERE runs before grouping.

2) You want to exclude products under $5 before grouping. Which clause?

WHERE price >= 5 . It tests a plain column on individual rows, so it belongs in WHERE — and trimming first is faster.

3) Why does HAVING items > 1 error when items is a SELECT alias?

HAVING is evaluated before SELECT assigns aliases, so the name isn't defined yet. Repeat the aggregate: HAVING COUNT(*) > 1 .

Combine WHERE , GROUP BY , and HAVING . Write it, then confirm in a playground.

Practice quiz

What does the HAVING clause filter?

  • Individual rows before grouping
  • Columns in the SELECT list
  • Groups produced by GROUP BY
  • The order of results

Answer: Groups produced by GROUP BY. HAVING is the WHERE for groups — it filters after GROUP BY using aggregate results.

Why can't you write WHERE COUNT(*) > 1?

  • WHERE runs before grouping, so COUNT(*) doesn't exist yet
  • COUNT is not a real function
  • WHERE only works on text
  • It would be too slow

Answer: WHERE runs before grouping, so COUNT(*) doesn't exist yet. Aggregates only exist after GROUP BY; WHERE runs before grouping, so put aggregate conditions in HAVING.

What is SQL's order of execution for these clauses?

  • SELECT, FROM, WHERE, GROUP BY, HAVING
  • WHERE, FROM, HAVING, GROUP BY, SELECT
  • FROM, GROUP BY, WHERE, SELECT, HAVING
  • FROM, WHERE, GROUP BY, HAVING, SELECT

Answer: FROM, WHERE, GROUP BY, HAVING, SELECT. Execution order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

A condition on a plain column (no aggregate) belongs in which clause?

  • HAVING
  • WHERE
  • GROUP BY
  • ORDER BY

Answer: WHERE. Plain column filters go in WHERE, which is faster because it trims rows before grouping.

An aggregate condition like AVG(price) > 20 belongs in which clause?

  • HAVING
  • WHERE
  • FROM
  • SELECT

Answer: HAVING. Conditions using COUNT/SUM/AVG/MIN/MAX go in HAVING, after grouping.

Why does HAVING items > 1 often fail when items is a SELECT alias?

  • items is a reserved word
  • Aliases are never allowed in any clause
  • HAVING is evaluated before SELECT assigns aliases
  • HAVING only accepts numbers

Answer: HAVING is evaluated before SELECT assigns aliases. HAVING runs before SELECT, so the alias isn't defined yet — repeat the aggregate: HAVING COUNT(*) > 1.

Which aggregates can HAVING use?

  • Only COUNT
  • COUNT, SUM, AVG, MIN, MAX
  • Only SUM and AVG
  • None — HAVING uses plain columns

Answer: COUNT, SUM, AVG, MIN, MAX. HAVING works with any aggregate function.

In a query with both, which runs first: WHERE or HAVING?

  • HAVING (it filters first)
  • They run at the same time
  • Whichever is written first
  • WHERE (it filters rows before grouping)

Answer: WHERE (it filters rows before grouping). WHERE trims rows before GROUP BY; HAVING then trims the resulting groups.

What is the portable, always-safe way to write a HAVING aggregate condition?

  • Use the SELECT alias
  • Repeat the full aggregate expression, e.g. HAVING COUNT(*) > 1
  • Use WHERE instead
  • Wrap it in parentheses

Answer: Repeat the full aggregate expression, e.g. HAVING COUNT(*) > 1. Repeating the aggregate avoids the alias trap and works in every engine.

What happens if you use HAVING without GROUP BY?

  • It always errors
  • It behaves exactly like WHERE
  • The whole result is treated as one group
  • It removes all rows

Answer: The whole result is treated as one group. Without GROUP BY the entire table is one group, so HAVING filters that single group.