Filtering Grouped Data with HAVING

The Problem: Filtering on Aggregated Values

Let's start with a common business request: "Show me a list of all countries where we have more than 5 customers."

You know how to count customers per country:

SELECT
  country,
  COUNT(*) AS customer_count
FROM customers
GROUP BY country;

A natural first attempt to filter this might be to use a WHERE clause, like this:

-- This query will fail!
SELECT
  country,
  COUNT(*) AS customer_count
FROM customers
WHERE COUNT(*) > 5 -- You cannot use an aggregate function here
GROUP BY country;

This query will produce an error. The reason is fundamental to how SQL works: the WHERE clause is processed before the GROUP BY clause and the aggregate functions are calculated. At the moment WHERE is doing its job, the database is looking at individual rows and has not yet calculated COUNT(*).

The Solution: The HAVING Clause

This is precisely the problem the HAVING clause was designed to solve. HAVING is used to filter data after it has been grouped by GROUP BY and the aggregate functions have been calculated.

The correct query for our request would be:

SELECT
  country,
  COUNT(*) AS customer_count
FROM customers
GROUP BY country
HAVING COUNT(*) > 5; -- The condition is applied to the grouped results

This query works because the HAVING clause is evaluated after the rows have been grouped by country and the COUNT(*) for each group has been computed.

Let's see a practical demonstration. This video provides a clear business scenario for using HAVING and shows the error that occurs when you try to use an aggregate function in WHERE.

The "Why": Understanding the Logical Order of SQL Operations

Your interest in low-level systems and computational thinking makes this the perfect time to go deeper. The reason WHERE and HAVING behave so differently comes down to the logical order of operations in a SQL query, which is not the same as the order in which you write the clauses (the lexical order).

For a developer, understanding this logical sequence is a high-ROI skill that demystifies many seemingly strange SQL behaviors.

As the article explains, the simplified logical order is:

1. FROM: Load the tables.
2. WHERE: Filter individual rows based on conditions.
3. GROUP BY: Arrange the filtered rows into groups.
4. aggregations: Compute aggregate functions (like COUNT(), SUM()) for each group.
5. HAVING: Filter the groups themselves based on the results of the aggregations.
6. SELECT: Determine the final columns to output.
7. ORDER BY: Sort the final result set.

This logical flow makes it clear: WHERE finishes its work at step 2, long before the aggregates are computed in step 4. HAVING, on the other hand, operates at step 5, so it has full access to the aggregated values.

This flowchart provides a great visual summary of the process:

Using WHERE and HAVING Together

You don't have to choose between WHERE and HAVING; you can, and often will, use them together in the same query. This allows you to perform two distinct levels of filtering:

• WHERE pre-filters the individual rows that go into the aggregation.
• HAVING post-filters the final groups based on the aggregated results.

Example Scenario: "From all sales transactions in 'Canada', find the cities where the total sales amount is greater than $1,000."

SELECT
  city,
  SUM(sale_amount) AS total_sales
FROM sales
WHERE country = 'Canada'    -- Step 1: Filter for rows from Canada only.
GROUP BY city               -- Step 2: Group the Canadian sales by city.
HAVING SUM(sale_amount) > 1000; -- Step 3: Filter for groups (cities) with total sales > 1000.

The next video demonstrates this combined usage effectively.

SELECT
  agent_name
FROM service_tickets
WHERE priority = 'High'
GROUP BY agent_name
HAVING COUNT(ticket_id) > 10;

Summary: WHERE vs. HAVING

To solidify your understanding, here is a concise breakdown of the key differences. This is a very common topic in technical interviews, so mastering this distinction has a high ROI for your job preparation goal.

Conclusion

You have now learned how to use the HAVING clause, a critical tool for any meaningful data analysis in SQL. More importantly, you've learned why it's necessary by understanding the logical order of query execution.

Key Takeaways:

• The HAVING clause is used to filter query results based on aggregate functions.
• The logical order of a query is FROM -> WHERE -> GROUP BY -> HAVING.
• Because WHERE is processed before GROUP BY, it cannot be used with aggregate functions.
• Because HAVING is processed after GROUP BY, it can be used with aggregate functions.
• WHERE and HAVING can be used together to apply both row-level and group-level filtering in a single query.

Preview of the Next Lesson:
Today, you learned the mechanics of HAVING and its relationship to WHERE. The next lesson in the syllabus is dedicated to mastering the distinction between them. Since this is such a crucial concept for both practical use and job interviews, we will dedicate the next session to reinforcing this knowledge with a variety of practice problems and scenarios. This will ensure you can confidently decide when to use WHERE, when to use HAVING, and when to use both.