HAVING

WHERE filters rows. But "which categories have at least two products?" is a question about groups, and groups don't exist until GROUP BY has run. That's what HAVING is for:

SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

HAVING works like WHERE, but on the grouped result, so it can test aggregates like COUNT(*).

WHERE can't do this

SELECT category, COUNT(*) AS products
FROM products
WHERE COUNT(*) >= 2
GROUP BY category;

The database refuses. Aggregates aren't allowed in WHERE, because WHERE looks at one row at a time, before any counting has happened.

More examples

Customers with more than one order:

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Departments where the average salary is above 100,000:

SELECT department, ROUND(AVG(salary)) AS avg_salary, COUNT(*) AS staff
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

WHERE and HAVING together

They often share a query: WHERE decides which rows go into the groups, and HAVING decides which groups come out. Customers who spent more than 200, not counting cancelled orders:

SELECT c.name, ROUND(SUM(oi.quantity * p.price), 2) AS spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
HAVING SUM(oi.quantity * p.price) > 200
ORDER BY spent DESC;

The order SQL runs a query

You write SELECT first, but the database works through the clauses in this order:

That's why WHERE can't see aggregates (step 2 comes before step 3), and why standard SQL won't let WHERE use an alias defined in SELECT. ORDER BY runs after SELECT, so it can.