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.

What You'll Learn

Our Sample Table: products

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

1. A Quick GROUP BY Recap

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.

2. HAVING — Filter the Groups

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.

3. WHERE vs HAVING — The Order of Execution

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 .

4. HAVING with Other Aggregates

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

Your Turn: cheap categories

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

5. Putting It All Together

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

6. The SELECT-Alias Trap

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.

Reorder Challenge

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

Quick Recall

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 .

Common Errors (and the fix)

📘 Quick Reference

Clause

Filters

Frequently Asked Questions

Mini-Challenge: Popular Price Tiers

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

🎉 Lesson Complete

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.

Continue this course