HAVING Clause in SQL for Filtering Aggregated Results

Once GROUP BY has organized rows into summarized groups, a natural next need arises: filtering out groups that do not meet a certain condition, such as showing only departments whose total salary exceeds a threshold, or only customers who placed more than five orders. This is exactly what the HAVING clause is designed for — filtering based on the result of an aggregate function, something a standard WHERE clause cannot do.

Key Definitions

• HAVING: A SQL clause that filters groups after aggregation has been applied, based on the result of an aggregate function.
• Aggregate condition: A filtering condition that references an aggregate function's computed value, such as SUM(salary) > 100000, which can only be evaluated in HAVING, not WHERE.
• Post-aggregation filter: Another term for HAVING's role, since it filters results after GROUP BY and aggregate functions have already been applied.

What You'll Learn

• Define HAVING and explain why it is needed in addition to WHERE.
• Write a GROUP BY query with HAVING to filter based on an aggregate function's result.
• Combine WHERE and HAVING in the same query, understanding their different roles.
• Recognize common business scenarios that specifically require HAVING.

Detailed Explanation

WHERE and HAVING both filter data, but they operate at fundamentally different stages of query execution. WHERE filters individual rows before any grouping or aggregation takes place — it decides which raw rows are even eligible to be grouped in the first place. HAVING, on the other hand, filters entire groups after GROUP BY and any aggregate functions have already been computed, meaning it can reference aggregate results directly, such as SUM(salary), COUNT(*), or AVG(order_amount), in its condition.

This distinction explains a rule that confuses many beginners: attempting to write WHERE SUM(salary) > 100000 produces an error, because at the point WHERE is evaluated, individual rows have not yet been grouped or aggregated, so SUM(salary) has no meaning yet. The correct approach is to move this condition into a HAVING clause instead: GROUP BY department_id HAVING SUM(salary) > 100000, which correctly evaluates the condition after each department's total salary has already been calculated.

A classic real-world use case for HAVING is finding 'high-value' or 'high-activity' groups: customers who placed more than five orders, departments whose average salary exceeds a benchmark, or products that generated more than a certain amount of revenue. All of these require first aggregating the data by group, and only then filtering out the groups that do not meet the specified threshold — precisely HAVING's purpose.

HAVING can be combined with WHERE in the same query when both types of filtering are needed simultaneously: WHERE might first exclude rows from an inactive year, and HAVING might then filter out departments whose total salary within that active-year data still falls below a threshold. Understanding that WHERE always executes first, narrowing the raw data, and HAVING always executes after grouping and aggregation, narrowing the summarized groups, is the key mental model for using both clauses correctly together.

Visual Summary

Draw a three-stage pipeline: Stage 1 'WHERE — filters individual rows before grouping' showing a full employees table narrowing down to a subset. Stage 2 'GROUP BY + aggregate functions — organizes remaining rows into groups and computes SUM per group' showing the subset collapsing into department-level summary rows. Stage 3 'HAVING — filters those summary groups' showing only departments with SUM(salary) > 100000 surviving into the final output.

Quick Reference

Clause	Filters What	Can Reference Aggregate Functions?	Executes Relative to Grouping
WHERE	Individual rows	No	Before GROUP BY and aggregation
HAVING	Entire groups	Yes	After GROUP BY and aggregation

SQL Example

-- INCORRECT: WHERE cannot reference an aggregate function directly
-- SELECT department_id, SUM(salary)
-- FROM employees
-- WHERE SUM(salary) > 100000   -- causes an error
-- GROUP BY department_id;

-- CORRECT: use HAVING to filter based on the aggregated SUM
SELECT
  department_id,
  SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;

-- Combining WHERE and HAVING together
SELECT
  department_id,
  SUM(salary) AS total_salary
FROM employees
WHERE hire_date >= '2024-01-01'
GROUP BY department_id
HAVING SUM(salary) > 100000;

The first commented query demonstrates the classic mistake of trying to use WHERE with an aggregate function, which is invalid since WHERE operates before aggregation exists. The corrected query moves the same condition into HAVING, correctly filtering departments after their total salary has been computed. The final query shows WHERE and HAVING working together: WHERE first narrows down to employees hired since 2024, and HAVING then filters out any department whose total salary, calculated only from those recent hires, still falls below 100000.

Real-World Examples

• Retention teams use HAVING to identify customers with COUNT(order_id) greater than a threshold, targeting loyal repeat customers for rewards programs.
• Finance teams use HAVING to flag departments where SUM(expenses) exceeds their allocated budget for the quarter.
• Sales managers use HAVING to identify product categories where AVG(order_amount) falls below an expected minimum, signaling a need for pricing review.
• HR analytics use HAVING to find departments where AVG(salary) is significantly below a company-wide benchmark, useful for pay equity investigations.
• Inventory teams use HAVING to identify warehouses where COUNT(low_stock_items) exceeds an acceptable threshold, prioritizing restocking efforts.

Common Mistakes to Avoid

• Attempting to use WHERE with an aggregate function directly, resulting in a query error.
• Using HAVING for a condition that could be more efficiently handled by WHERE on individual rows before grouping.
• Forgetting that HAVING conditions can reference aggregate functions not explicitly listed in the SELECT clause, as long as they are valid within the GROUP BY context.
• Confusing the order of execution, assuming HAVING runs before GROUP BY rather than after.

Interview Questions

Q1. What is the main difference between WHERE and HAVING?

WHERE filters individual rows before any grouping or aggregation occurs and cannot reference aggregate functions. HAVING filters entire groups after GROUP BY and aggregation have been applied, and can directly reference aggregate function results like SUM or COUNT.

Q2. Why does 'WHERE SUM(salary) > 100000' cause an error?

WHERE is evaluated before any grouping or aggregation takes place, so at that stage, SUM(salary) has not yet been computed and has no meaningful value to compare against. HAVING must be used instead, since it operates after aggregation.

Q3. Can WHERE and HAVING be used together in the same query?

Yes. WHERE typically filters raw rows before grouping (such as excluding inactive records), and HAVING then filters the resulting groups based on their aggregated values, allowing both types of filtering to work together in a single query.

Q4. Give a real business example that requires HAVING.

Identifying customers who have placed more than five orders requires first counting orders per customer (an aggregation) and then filtering out customers whose count does not exceed five, which is precisely what HAVING is designed to do.

Practice MCQs

1. Which clause is used to filter results based on an aggregate function's value?

1. WHERE
2. HAVING
3. ON
4. ORDER BY

Answer: B. HAVING

Explanation: HAVING filters entire groups after aggregation, allowing conditions that reference aggregate function results like SUM or COUNT.

2. Why can't WHERE reference an aggregate function like SUM(salary) directly?

1. WHERE only works with text columns
2. WHERE executes before grouping and aggregation occur
3. WHERE is deprecated in modern SQL
4. SUM cannot be used in any clause

Answer: B. WHERE executes before grouping and aggregation occur

Explanation: At the point WHERE is evaluated, rows have not yet been grouped or aggregated, so an aggregate function like SUM has no computed value yet to filter on.

3. In a query using both WHERE and HAVING, which one executes first?

1. HAVING
2. WHERE
3. They execute simultaneously
4. Neither has a defined order

Answer: B. WHERE

Explanation: WHERE filters raw rows before any grouping or aggregation; HAVING then filters the resulting groups afterward.

4. A report showing 'only customers who placed more than 5 orders' requires:

1. WHERE COUNT(*) > 5
2. GROUP BY combined with HAVING COUNT(*) > 5
3. ORDER BY COUNT(*) > 5
4. No aggregation is needed

Answer: B. GROUP BY combined with HAVING COUNT(*) > 5

Explanation: This requires first grouping orders by customer and counting them, then using HAVING to filter out customers whose order count does not exceed 5.

Quick Revision Points

• HAVING filters groups after aggregation; WHERE filters individual rows before aggregation.
• Only HAVING can reference aggregate function results like SUM(), COUNT(), or AVG() in its condition.
• WHERE and HAVING can be combined in the same query, with WHERE always executing first.
• This WHERE-versus-HAVING distinction is one of the most frequently tested SQL concepts in interviews and exams.

Conclusion

• HAVING exists specifically to filter based on aggregate function results, which WHERE cannot do.
• Understanding the execution order — WHERE first, then GROUP BY and aggregation, then HAVING — clarifies their distinct roles.
• Combining WHERE and HAVING in one query allows precise, efficient filtering at both the row and group level.
• Nearly every 'top performers' or 'threshold-based' business report relies on HAVING.

HAVING filters groups after GROUP BY and aggregate functions have already been applied, allowing conditions that directly reference aggregate results like SUM, COUNT, or AVG — something a WHERE clause cannot do, since WHERE operates on individual rows before any aggregation occurs. WHERE and HAVING can be combined in a single query, with WHERE always executing first to narrow raw rows, and HAVING executing afterward to filter the resulting summarized groups. This distinction underlies virtually every threshold-based or 'top performers' business report built in SQL.