🎓 A1Learner
🔍

SQL Having

HAVING filters groups after GROUP BY has formed them. WHERE filters single rows before any grouping happens.

Countries with more than one customer

The groups form first, one per country. HAVING then keeps only the groups whose count passes the test.

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1
ORDER BY country;

Categories with enough stock

HAVING here reads the SUM for each category group. Groups with a stock total under 200 drop out.

SELECT category, SUM(stock) AS stock_total
FROM products
GROUP BY category
HAVING SUM(stock) >= 200
ORDER BY category;

WHERE first, HAVING second

WHERE keeps only products priced under 20 before grouping starts. HAVING then keeps the categories that still hold two or more of those products.

SELECT category, COUNT(*) AS cheap_products
FROM products
WHERE price < 20
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

Example

SELECT category, SUM(stock) AS stock_total
FROM products
WHERE stock > 0
GROUP BY category
HAVING SUM(stock) > 100
ORDER BY stock_total DESC;

Key points

  • HAVING: the clause that filters groups after grouping
  • WHERE filters rows before GROUP BY forms the groups.
  • A HAVING test usually reads an aggregate value, like a count or a sum.
  • A group that fails the HAVING test leaves no row in the result.
  • WHERE and HAVING can work in the same query, each at its own stage.

In this example, WHERE runs first and drops the one product with zero stock. That product was the only Home row, so no Home group ever forms. GROUP BY then totals the stock per category. HAVING keeps the groups whose total passes 100, which drops Bags at 35. ORDER BY lists the surviving totals from largest to smallest.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

When does HAVING filter the data?