SQL HAVING

Basic HAVING Usage

SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING total > 5;

This query shows only departments with more than 5 employees.

WHERE vs HAVING

Both WHERE and HAVING are used to filter results, but they are applied at different stages of query execution:

WHERE filters rows before grouping or aggregation takes place. It works on individual rows in the table.
HAVING filters groups after aggregation has been performed (e.g., after GROUP BY and COUNT()).

Here’s an example combining both:

SELECT department, COUNT(*) AS total
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING total > 3;

In this query:

WHERE status = 'active' filters only the active employees before any grouping.
GROUP BY department groups the filtered rows by department.
HAVING total > 3 filters the grouped results to include only departments with more than 3 active employees.

Use WHERE to limit the dataset before aggregation, and HAVING when you need to filter based on aggregate functions like COUNT, SUM, AVG, etc.

HAVING with SUM and AVG

This returns only customers who spent more than 500 in total.

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 500;

Multiple HAVING Conditions

Filters categories with average prices between 10 and 100.

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price > 10 AND avg_price < 100;

HAVING with ORDER BY

Returns groups meeting the HAVING condition, sorted by count.

SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department
HAVING total > 2
ORDER BY total DESC;

Best Practices

Use WHERE for filtering individual rows
Use HAVING for filtering aggregated group results
Always use GROUP BY when applying HAVING
Alias your aggregates to make HAVING cleaner

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu