Historical Context & Motivation
The need to summarize large datasets did not originate with computers — accountants and statisticians have computed totals, averages, and counts for centuries. When E. F. Codd published his relational model in 1970, he formalized the idea that queries could both filter rows and compute summaries over them in a single declarative statement. The challenge of combining row-level predicates (deciding which rows participate) with aggregate functions (collapsing many rows into a single summary value) became one of the most important conceptual hurdles in query language design. Today, nearly every analytical SQL query interleaves WHERE filters with aggregate computations, making this interaction a cornerstone of database fluency.
The central question this lesson addresses is deceptively simple: When you write a WHERE clause alongside an aggregate function, which rows does the aggregate see? Misunderstanding this interaction is responsible for some of the most common SQL bugs — queries that silently return incorrect totals because the filter was placed in the wrong clause, or queries that fail to execute because an aggregate was referenced where only row-level expressions are permitted.
Core Principles & Definitions
Before exploring how WHERE and aggregates cooperate, it is essential to establish a precise vocabulary. SQL's logical processing order is the key to understanding every rule in this lesson: the database engine conceptually evaluates clauses in a fixed sequence, and each clause can only reference information available at its stage in that sequence.
Logical Processing Order
Row-Level vs. Group-Level
Aggregate Functions
The WHERE–HAVING Boundary
NULL Handling
Visual Explanation — SQL Logical Processing Pipeline
orders table are reduced to three by the WHERE clause before any aggregate function executes. The two cancelled rows never reach SUM or COUNT.The diagram above illustrates the most critical insight of this lesson: WHERE acts as a gatekeeper that determines which rows are eligible for aggregation. In the example, the original table contains five rows with a total amount of 2,100. After the WHERE clause filters out the two cancelled orders, the aggregate SUM(amount) computes 1,400 — reflecting only shipped orders. This is not a subtlety; it is the fundamental mechanism by which SQL separates row-level logic from set-level computation. Notice also that the HAVING clause, which appears downstream in the pipeline, is the only place where you can reference aggregate results to filter groups.
How WHERE and Aggregates Interact — The Execution Model
To reason precisely about SQL queries that mix filtering and aggregation, it helps to formalize the execution model. Although database engines use sophisticated optimizers that may reorder physical operations, the logical semantics are always defined by the standard processing order. This section presents the conceptual pipeline as a series of transformations on intermediate result sets.
WHERE COUNT(*) > 5 is a syntax error in every SQL dialect. At the point WHERE executes, no groups exist and no counts have been computed. The correct form is HAVING COUNT(*) > 5. However, you can use a subquery in WHERE that itself contains an aggregate — the subquery is a separate scope with its own processing pipeline.Understanding this constraint formally clarifies many design decisions. When you need to exclude individual rows that do not meet a condition — say, orders placed before a certain date — you use WHERE. When you need to exclude entire groups based on a summary statistic — say, customers with fewer than three orders — you use HAVING. The two clauses are not interchangeable; they operate at different stages of the pipeline and on different granularities of data.
Common Patterns — WHERE with Aggregates in Practice
In real-world SQL, the interplay between WHERE and aggregates appears in several recurring patterns. This section catalogs the most important ones, distinguishing correct usage from common anti-patterns. The visual below maps each pattern to its position in the logical pipeline.
| Pattern | SQL Example | Explanation |
|---|---|---|
| WHERE before aggregate (no GROUP BY) | SELECT AVG(salary) FROM employees WHERE dept = 'Eng'; | Filters to Engineering rows first, then computes average salary over that subset. |
| WHERE + GROUP BY + aggregate | SELECT dept, COUNT(*) FROM employees WHERE hire_date > '2020-01-01' GROUP BY dept; | Only employees hired after 2020 are counted; groups are formed from the filtered set. |
| WHERE + GROUP BY + HAVING | SELECT dept, SUM(salary) FROM employees WHERE status = 'active' GROUP BY dept HAVING SUM(salary) > 500000; | WHERE removes inactive employees; HAVING removes departments whose total salary for active employees is ≤ 500K. |
| Aggregate in subquery inside WHERE | SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); | The subquery computes a scalar aggregate independently; the outer WHERE compares each row's salary to that scalar. |
Worked Example — Revenue Report for Active Products
Consider a table sales with columns product_id, category, sale_amount, sale_date, and is_returned (boolean). We want to find the total revenue per category for non-returned sales in 2024, showing only categories with revenue exceeding $10,000.
is_returned = FALSE) and the sale must have occurred in 2024 (sale_date >= '2024-01-01' AND sale_date < '2025-01-01'). Both belong in the WHERE clause because they evaluate per-row attributes, not aggregate results.category. Only rows that survived the WHERE filter will form these groups.SUM(sale_amount) in the SELECT list. This function will sum only the non-returned 2024 sales within each category because of the WHERE clause.SELECT category, SUM(sale_amount) AS total_revenue FROM sales WHERE is_returned = FALSE AND sale_date >= '2024-01-01' AND sale_date < '2025-01-01' GROUP BY category HAVING SUM(sale_amount) > 10000 ORDER BY total_revenue DESC;is_returned check to HAVING would produce the same result. It would not, and in fact it would be syntactically invalid because is_returned is not an aggregate expression nor a grouped column (unless you group by it). Even if it were somehow valid, it would conceptually change the meaning: HAVING filters groups, not individual rows. Always ask: 'Does this condition apply to a single row or to a summary of many rows?'WHERE vs. HAVING — Strengths, Limitations & Performance
A thorough understanding of the WHERE–HAVING divide extends beyond correctness into performance and optimizer behavior. Modern query planners can sometimes push predicates between stages, but the logical semantics — and therefore the result set — are always governed by the standard processing order. The table below summarizes the key differences along multiple dimensions.
| Dimension | WHERE | HAVING |
|---|---|---|
| Evaluation Stage | Before GROUP BY and aggregation | After GROUP BY and aggregation |
| Operand Granularity | Individual rows | Groups (aggregated results) |
| Can Reference Aggregates? | No — syntax error if attempted | Yes — this is its primary purpose |
| Can Reference Non-Aggregated Columns? | Yes — any column from FROM tables | Only columns in GROUP BY list (or inside aggregate expressions) |
| Index Utilization | Can leverage B-tree and other indexes for fast row elimination | No index benefit — filtering occurs on computed aggregates |
| Effect on Aggregate Input | Reduces the number of rows aggregated — changes aggregate values | Does not change aggregate values — only removes entire groups from the output |
| Performance Implication | Generally faster to eliminate rows early, reducing work for GROUP BY and aggregation | Aggregation runs on all qualifying rows first; groups are filtered afterward |
Connection to Advanced Topics — Subqueries, CTEs, and Window Functions
The WHERE-before-aggregation rule provides the foundation for understanding more advanced SQL constructs. As queries grow in complexity, the interaction between filtering and aggregation manifests in several additional forms. Correlated subqueries allow aggregate values from one scope to be used in the WHERE clause of another scope. Common Table Expressions (CTEs) let you compute an aggregation step and then filter its results with a WHERE clause in the outer query. Window functions introduce a computation layer that occurs after WHERE but does not collapse rows, offering aggregate-like calculations without GROUP BY.
| Concept | Relationship to WHERE + Aggregates |
|---|---|
| Scalar Subquery in WHERE | A subquery like WHERE salary > (SELECT AVG(salary) FROM employees) is valid because the inner SELECT has its own pipeline. The aggregate resolves to a scalar before the outer WHERE evaluates it. |
| Correlated Subquery | A subquery referencing the outer table — e.g., WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept) — runs the inner aggregate for each outer row, enabling per-group comparisons at the row level. |
| CTE + WHERE | A CTE can pre-compute aggregates, and the outer query treats them as ordinary columns: WITH dept_totals AS (SELECT dept, SUM(salary) AS total FROM employees GROUP BY dept) SELECT * FROM dept_totals WHERE total > 500000; Here WHERE filters rows of the CTE, not the base table. |
| Window Functions | Window functions like SUM(salary) OVER (PARTITION BY dept) compute after WHERE but do not reduce rows. You cannot use window functions in WHERE directly; wrap them in a subquery or CTE if you need to filter on their results. |
As you progress to more complex analytics — rolling averages, percentile calculations, and multi-level aggregations — the conceptual model established in this lesson remains the invariant: WHERE always filters rows before aggregation within its scope. Subqueries and CTEs simply introduce new scopes, each with its own FROM → WHERE → GROUP BY → HAVING pipeline. Mastering this one principle makes every subsequent SQL concept easier to reason about.
Practice Problems
The following problems use a table called transactions with columns: id (INT), customer_id (INT), amount (DECIMAL), txn_date (DATE), txn_type (VARCHAR) with values 'purchase' or 'refund', and store_id (INT).
SELECT store_id, AVG(amount) FROM transactions WHERE AVG(amount) > 100 GROUP BY store_id; What is the correct way to express this intent?Summary — Aggregates with WHERE Filters
SQL's logical processing order — FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY — is the master key to understanding how aggregate functions interact with filters. The WHERE clause is a row-level filter that executes before any grouping or aggregation, determining which rows are eligible inputs to functions like SUM, COUNT, AVG, MIN, and MAX. Because aggregates have not yet been computed when WHERE runs, placing an aggregate function inside a WHERE clause is always a syntax error.
To filter on aggregate results, use HAVING, which evaluates after groups are formed. When a query requires both kinds of filtering, place row-level conditions in WHERE and group-level conditions in HAVING. This not only ensures correctness but also improves query performance by reducing the number of rows the engine must aggregate. For advanced scenarios, scalar subqueries and CTEs allow aggregate values to be used indirectly in WHERE by introducing a separate processing scope.