Aggregates with WHERE — Use aggregates with WHERE filters appropriately (conceptual)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).