HAVING for Group Filtering — Filter groups using HAVING - SQL

SECTION 1

Historical Context & Motivation

The relational model of data, proposed by E. F. Codd at IBM in 1970, introduced a mathematically rigorous foundation for organizing and querying structured data. As relational database management systems matured throughout the late 1970s and 1980s, practitioners discovered that simple row-level filtering with WHERE was insufficient for answering questions about groups of rows—questions like "which departments have more than five employees?" or "which products sold more than 1000 units last quarter?" These questions require first aggregating data and then applying a filter to the aggregated results, a two-phase operation that WHERE alone cannot express because WHERE evaluates before any grouping occurs.

1970 • Codd's Relational Model

E. F. Codd published "A Relational Model of Data for Large Shared Data Banks," establishing the theoretical underpinning for relational databases and set-based operations including grouping and aggregation.

1974 • SEQUEL Language at IBM

Donald Chamberlin and Raymond Boyce designed SEQUEL (later SQL) at IBM's San Jose Research Lab. Early prototypes already supported GROUP BY, but lacked a dedicated clause for filtering grouped results.

1986 • SQL-86 Standard (ANSI)

The first ANSI SQL standard formalized the HAVING clause alongside GROUP BY, establishing the logical query processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

1992 • SQL-92 Enhancements

SQL-92 expanded HAVING's capabilities with subqueries and richer predicate support, enabling sophisticated group-level filtering such as comparing group aggregates against correlated subquery results.

2003+ • Modern SQL & Window Functions

Subsequent standards introduced window functions (OVER clause), which complement HAVING by enabling per-row computations against group aggregates without collapsing rows—further clarifying HAVING's specific role in aggregate filtering.

The fundamental question that motivated the HAVING clause is deceptively simple: how do you filter rows based on properties that only exist after aggregation? Since WHERE is evaluated before GROUP BY in the logical query processing pipeline, it has no access to aggregate values like COUNT, SUM, or AVG. The HAVING clause was introduced specifically to bridge this gap, operating after groups are formed and aggregate functions are computed.

SECTION 2

Core Principles & Definitions

Understanding HAVING requires a firm grasp of SQL's logical query processing order. Although a SQL engine's physical execution plan may reorder operations for optimization, the logical evaluation sequence determines what data each clause can reference. The HAVING clause occupies a critical position in this sequence—after grouping and aggregation but before the final projection in SELECT. This positioning is what gives HAVING its unique power: it can reference aggregate functions that WHERE cannot.

Post-Aggregation Filter

HAVING evaluates conditions after GROUP BY has partitioned rows into groups and aggregate functions (COUNT, SUM, AVG, MIN, MAX) have been computed. It eliminates entire groups that fail the predicate.

WHERE vs. HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Using WHERE for pre-filtering reduces the data that enters GROUP BY, improving performance.

Aggregate Function Requirement

HAVING conditions typically involve at least one aggregate function. While syntactically you can use HAVING on non-aggregated columns from the GROUP BY list, doing so is semantically equivalent to WHERE and is discouraged.

Logical Query Order

The standard evaluation order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. HAVING sits between GROUP BY and SELECT, making it the gatekeeper for which groups appear in results.

Composability with Boolean Logic

HAVING supports AND, OR, NOT, and parenthesized compound predicates, allowing complex multi-condition group filters such as groups with high counts AND above-average sums.

✦

KEY TAKEAWAY

Think of a university registrar compiling grade reports. WHERE is like checking each student's ID before they enter the exam hall—it acts on individual records. GROUP BY is like sorting completed exams into piles by course section. HAVING is the registrar looking at each pile and saying, "Only show me sections where the average score exceeds 75." You cannot make that decision until all exams in each pile have been graded and averaged—which is exactly why HAVING must execute after GROUP BY.

SECTION 3

Visual Explanation — SQL Logical Processing Pipeline

The diagram shows the six major phases of SQL logical query processing. Notice that HAVING (step 4) executes after GROUP BY (step 3) but before SELECT (step 5). This positioning is why HAVING can reference aggregate functions—the groups and their computed aggregates already exist when HAVING evaluates its predicates.

The pipeline diagram above illustrates a critical invariant: each clause can only reference data that has been produced by preceding stages. Since WHERE (step 2) executes before GROUP BY (step 3), it sees only raw, ungrouped rows and has no concept of aggregate values. Attempting to write WHERE COUNT(*) > 5 is a semantic error in every SQL dialect—the COUNT aggregate simply does not exist at that stage. The HAVING clause was designed precisely to fill this gap, providing a filter checkpoint after groups and their aggregates have been materialized.

SECTION 4

How HAVING Works — Syntax and Semantics

The HAVING clause follows a straightforward syntactic pattern but carries deep semantic implications related to the relational algebra's selection operator (σ) applied to grouped relations. Let us examine the canonical syntax and its formal underpinnings.

CANONICAL SYNTAX

SELECT col₁, AGG(col₂) FROM table [WHERE row_condition] GROUP BY col₁ HAVING AGG(col₂) <operator> value [ORDER BY …];

AGG = any aggregate function (COUNT, SUM, AVG, MIN, MAX). The operator can be =, <>, <, >, <=, >=, BETWEEN, IN, or LIKE when applied to string aggregates.

RELATIONAL ALGEBRA CORRESPONDENCE

σ_condition( 𝒢_{col₁, AGG(col₂)}(σ_where(R)) )

Where σ denotes the selection operator, 𝒢 denotes the grouping/aggregation operator applied to relation R after the WHERE selection. The HAVING condition is the outermost σ applied to the grouped result.

Evaluation Semantics Step by Step

Step 1: The engine reads rows from the FROM source and applies WHERE to eliminate rows that do not satisfy the row-level predicate.
Step 2: GROUP BY partitions the surviving rows into groups—one group per distinct combination of grouping columns.
Step 3: Aggregate functions (COUNT, SUM, AVG, etc.) are computed for each group independently.
Step 4: HAVING evaluates its predicate against each group's aggregate values. Groups that return FALSE or NULL are discarded.
Step 5: SELECT projects the requested columns and aggregate expressions from the surviving groups.

⚠

Common Pitfall

Placing a condition in HAVING that could be placed in WHERE is a performance antipattern. If a predicate does not involve an aggregate function, it should go in WHERE so that rows are eliminated before grouping, reducing the number of rows the GROUP BY operator must process. For example, HAVING department = 'Sales' is legal but wasteful; use WHERE department = 'Sales' instead.

SECTION 5

Data Flow — From Raw Rows to Filtered Groups

To solidify understanding, let us trace a concrete data set through each stage of the pipeline. Consider a table called orders with columns customer_id, amount, and order_date. We want to find customers whose total spending exceeds $500.

This diagram traces the data from the raw orders table through GROUP BY (producing three groups for customers A, B, and C with their SUM aggregates) and finally through HAVING, which eliminates customer B's group because 250 < 500. Only groups satisfying the predicate (A = 550, C = 600) appear in the final result.

A few observations from the trace above deserve emphasis. First, HAVING operates at the group level, not the row level—when customer B's group is eliminated, all rows belonging to B are gone, not selectively. Second, the aggregate expression in HAVING (SUM(amount)) does not need to appear in the SELECT list, although most engines support it in both places. Third, had we added a WHERE clause (e.g., WHERE order_date >= '2024-01-01'), it would have filtered individual rows before GROUP BY even formed the groups, potentially changing which customers survive the HAVING threshold.

SECTION 6

Worked Example — Finding High-Volume Departments

Suppose we have an employees table with columns emp_id, department, salary, and hire_date. We want to find departments with more than 3 employees where the average salary exceeds $70,000, but only considering employees hired after 2020.

Multi-condition HAVING with WHERE Pre-filter

1
Step 1 — Identify the row-level filter (WHERE)

The requirement "only considering employees hired after 2020" is a row-level condition that does not involve aggregation. This belongs in WHERE: WHERE hire_date > '2020-12-31'. Placing it here reduces the number of rows entering GROUP BY.

WHERE hire_date > '2020-12-31'
2
Step 2 — Define the grouping column (GROUP BY)

We want statistics per department, so we group by the department column: GROUP BY department. After this step, each unique department value forms one group containing all its post-2020 employees.

GROUP BY department
3
Step 3 — Define the group-level filters (HAVING)

Two conditions involve aggregate functions: (1) "more than 3 employees" translates to COUNT(*) > 3, and (2) "average salary exceeds $70,000" translates to AVG(salary) > 70000. Both must be true simultaneously, so we combine them with AND.

HAVING COUNT(*) > 3 AND AVG(salary) > 70000
4
Step 4 — Define the projection (SELECT)

We project the department name alongside the aggregates we computed. Although HAVING references COUNT(*) and AVG(salary), we can choose which to display in the final output.

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
5
Step 5 — Assemble the complete query

Combining all clauses in the correct syntactic order yields the final query. Note that the logical processing order (FROM → WHERE → GROUP BY → HAVING → SELECT) differs from the written order (SELECT … FROM … WHERE … GROUP BY … HAVING …).

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary FROM employees WHERE hire_date > '2020-12-31' GROUP BY department HAVING COUNT(*) > 3 AND AVG(salary) > 70000 ORDER BY avg_salary DESC;

SECTION 7

WHERE vs. HAVING — Comparison and Best Practices

The most common source of confusion for SQL learners is knowing when to use WHERE versus HAVING. While both are filter mechanisms, they operate at fundamentally different stages of query processing and serve distinct purposes. The table below provides a systematic comparison across several dimensions.

Dimension	WHERE	HAVING
Evaluation stage	Before GROUP BY (step 2)	After GROUP BY (step 4)
Operates on	Individual rows	Groups of rows
Can reference aggregates?	No	Yes
Requires GROUP BY?	No	Typically yes (some engines allow HAVING without GROUP BY, treating entire table as one group)
Performance impact	Reduces rows before grouping (preferred for non-aggregate conditions)	Filters after full grouping and aggregation have occurred
Example predicate	`WHERE status = 'active'`	`HAVING COUNT(*) > 10`

Systematic comparison of WHERE and HAVING clauses

🧭

DECISION HEURISTIC

Apply a simple rule: if the condition can be evaluated by looking at a single row in isolation, it belongs in WHERE. If the condition requires knowledge of multiple rows within a group (i.e., it uses an aggregate function), it belongs in HAVING. Think of it like sorting mail: WHERE is the mail carrier who discards undeliverable letters before putting them in mailboxes (groups). HAVING is the mailbox owner who looks at everything collected and decides whether the pile is worth keeping.

💡

Performance Tip

Always push non-aggregate predicates into WHERE rather than HAVING. If your query includes HAVING department = 'Engineering' AND COUNT(*) > 5, refactor to WHERE department = 'Engineering' ... HAVING COUNT(*) > 5. This reduces the data volume entering the GROUP BY operator, which can significantly improve query execution time on large tables.

SECTION 8

Connection to Advanced SQL Features

HAVING is a foundational clause, but modern SQL provides several advanced features that interact with or extend its capabilities. Understanding how HAVING relates to window functions, CTEs (Common Table Expressions), and subqueries is essential for writing idiomatic, performant queries in production systems.

Feature	HAVING Approach	Advanced Approach
Filter on aggregate	`GROUP BY dept HAVING AVG(sal) > 60000`	CTE computes aggregate, outer query filters with WHERE on the CTE's output
Retain individual rows	Not possible — HAVING collapses to group-level output	Window function: `AVG(sal) OVER (PARTITION BY dept)` with QUALIFY or subquery filter
Compare group to global	`HAVING AVG(sal) > (SELECT AVG(sal) FROM employees)`	Scalar subquery inside HAVING — this is the standard approach
Multi-level grouping	HAVING applies once after the final GROUP BY	ROLLUP / CUBE with GROUPING SETS for hierarchical aggregation; HAVING still usable on each level

HAVING vs. advanced SQL alternatives

A particularly powerful pattern is the correlated subquery inside HAVING. For example, to find departments whose average salary exceeds the company-wide average, you can write: HAVING AVG(salary) > (SELECT AVG(salary) FROM employees). The subquery executes once and provides a scalar benchmark, while HAVING applies that benchmark group by group. As you advance into courses on query optimization, you will encounter how the query planner handles such patterns—often by materializing the subquery result once and reusing it across all group evaluations.

SECTION 9

Practice Problems

The following problems use a database schema with three tables: students(student_id, name, major), enrollments(enrollment_id, student_id, course_id, grade), and courses(course_id, title, department, credits). Grades are stored as numeric values (0.0 to 4.0).

Question 1: CONCEPTUAL

Explain why the query SELECT major, COUNT(*) FROM students WHERE COUNT(*) > 5 GROUP BY major produces a syntax error. Where should the aggregate condition be placed, and why?

Show answer

The query fails because WHERE is evaluated before GROUP BY in the logical processing order, meaning aggregate functions like COUNT() do not yet exist at that stage. The engine cannot compute COUNT() until rows have been partitioned into groups. The correct approach is to move the aggregate condition to the HAVING clause: SELECT major, COUNT(*) FROM students GROUP BY major HAVING COUNT(*) > 5. HAVING evaluates after GROUP BY, so COUNT(*) is available. This distinction is fundamental to SQL's declarative evaluation model.

Question 2: BASIC CALCULATION

Write a query to find all departments (from the courses table) that offer more than 10 courses. Display the department name and the course count, ordered by count descending.

Show answer

The solution groups courses by department, counts them, and filters with HAVING:

SELECT department, COUNT(*) AS course_count FROM courses GROUP BY department HAVING COUNT(*) > 10 ORDER BY course_count DESC;

Here, GROUP BY department creates one group per department, COUNT() computes the number of courses in each group, and HAVING COUNT() > 10 retains only departments with more than 10 courses. The ORDER BY is evaluated last and sorts the surviving groups.

Question 3: INTERMEDIATE

Write a query to find students who have enrolled in at least 4 courses and maintain a GPA (average grade) above 3.5. Display student_id, number of courses, and their GPA. Use both WHERE and HAVING appropriately.

Show answer

This problem requires joining enrollments (for grades and course counts) and grouping by student:

SELECT e.student_id, COUNT(e.course_id) AS num_courses, AVG(e.grade) AS gpa FROM enrollments e GROUP BY e.student_id HAVING COUNT(e.course_id) >= 4 AND AVG(e.grade) > 3.5 ORDER BY gpa DESC;

Both conditions (count ≥ 4 and average > 3.5) involve aggregate functions, so both correctly belong in HAVING. If we wanted to exclude specific courses (e.g., pass/fail courses), that row-level filter would go in WHERE before the GROUP BY.

Question 4: APPLIED

The registrar wants a report of CS department courses where the average student grade is below 2.0 (indicating courses students struggle with), but only considering courses with at least 20 enrolled students. Write the query using the enrollments and courses tables.

Show answer

This requires a join between courses and enrollments, a WHERE clause to pre-filter to the CS department (a row-level condition), and HAVING for the aggregate conditions:

SELECT c.course_id, c.title, COUNT(e.student_id) AS enrolled, AVG(e.grade) AS avg_grade FROM courses c JOIN enrollments e ON c.course_id = e.course_id WHERE c.department = 'CS' GROUP BY c.course_id, c.title HAVING COUNT(e.student_id) >= 20 AND AVG(e.grade) < 2.0 ORDER BY avg_grade ASC;

Notice that department = 'CS' is in WHERE (not HAVING) because it filters individual rows before grouping—this is both semantically correct and more performant, as it reduces the data entering the GROUP BY operator.

Question 5: CRITICAL THINKING

Consider the query:

SELECT major, AVG(g.grade) AS avg_gpa FROM students s JOIN enrollments g ON s.student_id = g.student_id GROUP BY major HAVING AVG(g.grade) > (SELECT AVG(grade) FROM enrollments)

. Explain (a) what this query returns, (b) when the subquery in HAVING is evaluated relative to the outer query's GROUP BY, and (c) whether the subquery is correlated or uncorrelated and what the performance implications are.

Show answer

(a) The query returns majors whose students' average grade exceeds the university-wide average grade across all enrollments. (b) The subquery (SELECT AVG(grade) FROM enrollments) is uncorrelated—it does not reference any column from the outer query—so the optimizer typically evaluates it once, materializes the scalar result, and reuses that value for every group comparison in HAVING. (c) Because it is uncorrelated, it runs in O(n) time to scan the enrollments table once, then the scalar is compared against each group's AVG. If it were correlated (referencing, say, s.major), it would conceptually re-execute per group, though modern optimizers may decorrelate it. The key insight is that HAVING supports subqueries, enabling comparisons between per-group aggregates and global aggregates—a pattern frequently used in analytical queries.

SUMMARY

Summary — HAVING for Group Filtering

Summary

The HAVING clause is SQL's mechanism for filtering groups produced by GROUP BY based on aggregate function results. It occupies a unique position in the logical query processing order—after GROUP BY and aggregation but before SELECT—which gives it access to computed values like COUNT, SUM, AVG, MIN, and MAX that the WHERE clause cannot reference.

The fundamental distinction between WHERE and HAVING is their scope: WHERE filters individual rows before grouping, while HAVING filters entire groups after aggregation. For optimal performance, non-aggregate conditions should always be placed in WHERE to reduce the data volume entering GROUP BY. HAVING supports compound predicates with AND, OR, and NOT, as well as subqueries for comparing group aggregates against global benchmarks—making it an indispensable tool in the SQL practitioner's arsenal.