Skip to content
SQLSimplified
group by

GROUP BY with HAVING

Use HAVING to keep only departments whose average salary exceeds a threshold after aggregation.

Overview

HAVING filters groups after aggregation, the group-level equivalent of WHERE, which filters individual rows before grouping. Use it when the condition depends on an aggregate (like "departments with average salary over $90,000"), which WHERE cannot reference.

The Query

Loading playground environment...

Step-by-Step Breakdown

  1. GROUP BY d.name: aggregates per department.
  2. HAVING AVG(e.salary) > 90000: drops groups whose average pay is at or below $90k. This can't be a WHERE clause because AVG isn't known yet.
  3. WHERE (if any) would run before grouping; HAVING runs after.

WHERE vs HAVING

WHERE e.salary > 90000 would drop low-salary employees first; HAVING AVG(e.salary) > 90000 drops low-average departments. Very different results.

Variations

Keep only categories with more than 2 orders:

Loading playground environment...

Common Mistakes

  • Using WHERE on an aggregate. WHERE AVG(salary) > 90000 is a syntax error; aggregates belong in HAVING.
  • Referencing ungrouped columns in HAVING. Just like SELECT, HAVING expressions must use grouped or aggregated columns.
  • Forgetting GROUP BY. HAVING without a GROUP BY treats the whole table as one group.

Related examples

GROUP BY Basics: Aggregating Rows Into Summaries
group by
Collapse individual orders into one summary row per customer using GROUP BY, COUNT, and SUM.
GROUP BY Multiple Columns
group by
Group by both category and status to see order counts for every product-category/status combination.
JOIN Followed by GROUP BY
join
Join employees to departments, then aggregate to compute headcount and average salary per department.

Cite this resource

SQLSimplified. "GROUP BY with HAVING Example". Available at: https://sqlsimplified.online/examples/group-by-having