WHERE vs. HAVING: Filtering Aggregate Queries

Hello! Last lesson, you used GROUP BY to turn individual sales into summaries at a chosen grain, such as one row per category. You also saw that a category’s SUM(amount) depends on which sales contribute to that category.

This lesson adds a decision that matters in nearly every aggregate query: should a condition remove individual sales before the totals are calculated, or remove category summaries afterward? You’ll use WHERE for the first job and HAVING for the second, then combine them in one query.

Two different things you can filter

We’ll continue with the same sales table. Each source row represents one sale:

Without filtering, grouping by category gives books a total of 85 and games a total of 60. Now compare two questions:

• “What are the totals from web sales, per category?” Only web rows should contribute to each total.
• “Which categories have total sales above 60?” All sales should contribute to each category total, but only qualifying category summaries should appear.

Both questions involve a filter and an aggregate. What differs is the unit being tested: a source row in the first question, an aggregated group in the second.

This is a useful logical order for reading an aggregate query: SQL considers the rows supplied by FROM, applies WHERE to those rows, forms groups and computes aggregates, then applies HAVING to the groups. You write the clauses in their usual SQL order—WHERE before GROUP BY, and HAVING after it.

Use WHERE to decide which rows enter the calculation

For “total web sales per category,” channel = 'web' is a property of each individual sale. Put that condition in WHERE:

SELECT
    category,
    COUNT(*) AS sale_rows,
    SUM(amount) AS total_amount
FROM sales
WHERE channel = 'web'
GROUP BY category
ORDER BY category;

The books calculation receives amounts 20 and 30; its store sales never enter the calculation. The games calculation receives 40 and 5. Notice that WHERE did more than hide some output: it changed what the aggregate measures.

This is why you should be cautious about moving a condition between clauses. Suppose you want total sales for every category, but you only want to display certain categories. A row filter on amount would change the totals by excluding sales. It would not merely hide category summaries.

A condition on a grouping column can also go in WHERE. For example, WHERE category = 'books' restricts input to books before grouping. If your question only concerns books, that is generally clearer than forming every category group and filtering to books later.

Use HAVING to decide which summaries remain

Return to the question “Which categories have total sales above 60?” To answer it, the database must first add the sales within each category. That makes SUM(amount) > 60 a group condition:

SELECT
    category,
    COUNT(*) AS sale_rows,
    SUM(amount) AS total_amount
FROM sales
GROUP BY category
HAVING SUM(amount) > 60
ORDER BY category;

Games is absent because its full category total is 60, which does not satisfy > 60. Books still shows all four of its sales and the full total of 85: HAVING excluded the games summary, not individual games rows while the sums were being calculated.

It would be invalid to write WHERE SUM(amount) > 60 in this query. At the WHERE stage, SQL is testing individual rows; the category total has not been computed. Use an aggregate expression such as SUM(amount) or COUNT(*) in HAVING when the condition depends on a group metric.

For portability between SQL systems, repeat the aggregate expression in HAVING as shown, rather than relying on a SELECT alias such as total_amount being available there. The alias remains a helpful label for the result.

Use both when the question has both kinds of condition

Now ask: “Among web sales, which categories have at least two sales and a total amount of at least 50?” There are two decisions here. “Web” determines which sales qualify; the count and total determine which category summaries qualify.

SELECT
    category,
    COUNT(*) AS sale_rows,
    SUM(amount) AS total_amount
FROM sales
WHERE channel = 'web'
GROUP BY category
HAVING COUNT(*) >= 2
   AND SUM(amount) >= 50
ORDER BY category;

The result contains only books, with sale_rows = 2 and total_amount = 50. Games also has two web sales, but their total is 45.

A reliable way to check this query is to trace the two categories separately. After WHERE, books has web amounts 20 and 30, while games has 40 and 5. Grouping produces two summaries. HAVING keeps books and removes games. At no point does the query count store sales toward its web-sales threshold.

The position of WHERE in the written query matters for interpretation:

FROM sales
WHERE channel = 'web'
GROUP BY category
HAVING SUM(amount) >= 50

Here, “at least 50” means at least 50 in web sales. If you removed WHERE, it would mean at least 50 across all sales instead. An unchanged HAVING condition can therefore answer a different question when you change the input rows.

A decision rule you can reuse

Before placing a condition, state what one output row represents. Then ask whether the condition can be evaluated for one source row, or needs the completed output group.

A condition that refers only to a grouping column, such as category = 'books', can usually be expressed in either place. Prefer WHERE when you intend to restrict input: it makes that intention explicit and avoids calculating groups you already know you will exclude. By contrast, a condition on SUM(amount) cannot be moved into WHERE without changing the question—or producing an error.

HAVING is not limited to queries with an explicit GROUP BY. An aggregate over the entire input has a single overall group, which HAVING can also test. In most category-level questions, though, you will see it immediately after GROUP BY, testing each category’s completed metrics.

Takeaway

WHERE determines which rows contribute to an aggregate. HAVING determines which aggregated groups appear in the result. When a question contains both kinds of restriction, use both clauses and check each one against the intended result grain.

Next, you’ll build on these category summaries to calculate several category-level metrics in one query using conditional aggregation.