The HAVING Clause

Reviewed & published by Brayan K

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.

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

• Filter groups with HAVING and an aggregate
• Tell WHERE and HAVING apart for good
• Understand SQL's true order of execution
• Combine WHERE + GROUP BY + HAVING
• Use HAVING with COUNT, SUM, AVG, MIN, MAX
• Avoid the SELECT-alias trap in HAVING

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.

-- The table this lesson uses:
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- Recap: GROUP BY collapses rows into groups, one row per group.
SELECT category, COUNT(*) AS items, SUM(price) AS total
FROM products
GROUP BY category;

-- ✅ Expected result:
-- category | items | total
-- Electronics | 3 | 116.98
-- Home | 1 | 32
-- Kitchen | 1 | 9.5
-- Stationery | 1 | 3.25

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.

-- Uses the products table from the first example.
-- HAVING filters the GROUPS — using aggregate results.
SELECT category, COUNT(*) AS items
FROM products
GROUP BY category
HAVING COUNT(*) > 1;     -- keep only categories with more than 1 product

-- WHERE can't do this: COUNT(*) doesn't exist until AFTER grouping.

-- ✅ Expected result:
-- category | items
-- Electronics | 3

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.

-- Uses the products table from the first example.
-- WHERE filters ROWS before grouping; HAVING filters GROUPS after.
SELECT category, AVG(price) AS avg_price
FROM products
WHERE price > 5            -- 1) drop cheap rows FIRST
GROUP BY category          -- 2) then group what remains
HAVING AVG(price) > 20;    -- 3) keep only pricey groups

-- Order of execution: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT.

-- ✅ Expected result:
-- category | avg_price
-- Electronics | 38.99333333333333
-- Home | 32

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.

-- Uses the products table from the first example.
-- HAVING works with any aggregate: SUM, AVG, MIN, MAX, COUNT.
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category
HAVING SUM(stock) >= 200
ORDER BY total_stock DESC;

-- ✅ Expected result:
-- category | total_stock
-- Stationery | 500
-- Electronics | 365
-- Kitchen | 300

Your Turn: cheap categories

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

-- Uses the products table from the first example.
-- 🎯 YOUR TURN — fill in the blanks
-- Goal: show categories whose AVERAGE price is below 15.

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
____ AVG(price) < ____;    -- 👉 keyword = HAVING, value = 15

-- ✅ Expected:
-- category   | avg_price
-- Kitchen    | 9.50
-- Stationery | 3.25

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.

-- Uses the products table from the first example.
-- A realistic report: WHERE + GROUP BY + HAVING together.
SELECT
    category,
    COUNT(*)   AS in_stock_items,
    AVG(price) AS avg_price
FROM products
WHERE stock > 0                 -- only sellable items
GROUP BY category
HAVING COUNT(*) >= 1            -- group must have at least 1
   AND AVG(price) < 50          -- and be affordable on average
ORDER BY avg_price;

-- ✅ Expected result:
-- category | in_stock_items | avg_price
-- Stationery | 1 | 3.25
-- Kitchen | 1 | 9.5
-- Home | 1 | 32
-- Electronics | 3 | 38.99333333333333

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.

-- Uses the products table from the first example.
-- A common trap: HAVING usually can't see SELECT aliases.
-- This FAILS in many engines because "items" isn't defined yet:
SELECT category, COUNT(*) AS items
FROM products
GROUP BY category
HAVING items > 1;        -- ❌ error in standard SQL

-- Repeat the aggregate instead — always safe:
SELECT category, COUNT(*) AS items
FROM products
GROUP BY category
HAVING COUNT(*) > 1;     -- ✅ works everywhere

-- ✅ Expected result:
-- category | items
-- Electronics | 3

Reorder Challenge

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

A.   HAVING SUM(stock) > 200
B.   GROUP BY category
C.   SELECT category, SUM(stock)
D.   WHERE price > 5
E.   FROM products

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)

• Aggregate in WHERE: WHERE COUNT(*) > 1 errors. Move it to HAVING.
• Plain column in HAVING: works, but it's slower and confusing — put column filters in WHERE.
• SELECT alias in HAVING: non-portable. Repeat the full aggregate expression.
• HAVING with no GROUP BY by accident: treats the whole table as one group, which may not be what you meant.
• Wrong clause order: HAVING must come after GROUP BY and before ORDER BY.

Mini-Challenge: Popular Price Tiers

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

-- Uses the products table from the first example.
-- 🎯 MINI-CHALLENGE
-- Using WHERE, GROUP BY, and HAVING on the products table:
--   1. Consider only products with price >= 10
--   2. Group by category
--   3. Keep only categories with 2 or more such products
--   4. Show category and the count, highest count first
--
-- ✅ Expected: Electronics | 2  (Keyboard 79.00, USB-C 12.99 >= 10)

-- your query here

🎉 Lesson Complete

• ✅ HAVING filters groups using aggregate results
• ✅ WHERE filters rows before grouping; HAVING filters after
• ✅ Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT
• ✅ HAVING works with COUNT, SUM, AVG, MIN, MAX
• ✅ Repeat the aggregate in HAVING — don't rely on SELECT aliases
• ✅ Next: UNION, INTERSECT & EXCEPT — combining result sets

Practice quiz

Continue this course

• Previous: GROUP BY & HAVING
• Next: Subqueries — Nest queries inside other queries for complex data retrieval
• Quick reference: SQL cheat sheet › Aggregation

Frequently asked questions

What's the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped, and it cannot use aggregate functions. HAVING filters the groups produced by GROUP BY and is specifically for aggregate conditions like COUNT(*) > 5. A handy rule: WHERE comes before GROUP BY, HAVING comes after.

Can I use HAVING without GROUP BY?

Technically yes — without GROUP BY the whole result is treated as one group, so HAVING filters that single group. For example SELECT COUNT(*) FROM products HAVING COUNT(*) > 100 returns the count only if there are more than 100 rows. In practice, though, HAVING almost always pairs with GROUP BY.

Why can't I just use WHERE COUNT(*) > 1?

Because WHERE runs before grouping happens, so COUNT(*) doesn't exist yet at that stage. Aggregates are only available after GROUP BY, which is exactly where HAVING operates. Trying to put an aggregate in WHERE produces an error.

Can HAVING reference a column alias from SELECT?

In standard SQL, no — HAVING is evaluated before SELECT assigns aliases, so you should repeat the full aggregate expression (HAVING COUNT(*) > 1, not HAVING items > 1). Some engines like MySQL allow the alias as an extension, but repeating the aggregate is portable and always safe.

Related lessons

• type conversion