Filtering Grouped Data with HAVING

The Problem: Filtering on an Aggregate Value

As we just discussed, the WHERE clause is executed before the GROUP BY clause. This means that when WHERE is doing its work, the aggregated values (like SUM(cost) for a whole group) haven't been calculated yet. Trying to use an aggregate function in a WHERE clause will result in an error.

Let's see this in action.

The Solution: The HAVING Clause

The HAVING clause was designed specifically to solve this problem. It is used to filter groups after the aggregations have been performed.

The logical order of execution for a query that includes HAVING is:

1. FROM: Gets the table.
2. WHERE: Filters individual rows.
3. GROUP BY: Groups the remaining rows.
4. HAVING: Filters the groups based on an aggregate condition.
5. SELECT: Selects the final columns.
6. ORDER BY: Sorts the final result.

Because HAVING operates on the grouped data, you can use aggregate functions like COUNT(), SUM(), and AVG() in its conditions.

Now, let's see the correct way to write the query from the previous video.

WHERE vs. HAVING: The Definitive Guide

The most common point of confusion for new SQL users is when to use WHERE and when to use HAVING. The following reading provides an excellent breakdown.

To summarize the key distinction:

• Use WHERE to filter individual rows based on column values. This happens before aggregation. (e.g., WHERE region = 'USA')
• Use HAVING to filter entire groups based on aggregate function results. This happens after aggregation. (e.g., HAVING SUM(sales) > 10000)

You can, and often will, use both in the same query. For example, in an AdTech context:

SELECT
  campaign_id,
  SUM(cost) AS total_campaign_cost
FROM campaign_events
WHERE
  event_date >= '2023-01-01' -- Filters individual events to only include this year's data
GROUP BY
  campaign_id
HAVING
  SUM(cost) > 5000; -- Filters groups to show only high-spend campaigns

This query first selects only the events from 2023, then groups them by campaign, and finally keeps only those campaigns whose total cost in 2023 exceeded $5000.

Practical Application: Identifying High-Value Customers

Let's bring this into a realistic business analysis scenario that aligns with your goal of preparing for job interviews. A common task for a BA is to analyze customer data to identify valuable segments.

Imagine you're in a mock interview. You're given two tables, user_sessions and attributions, and asked to identify "high-value users," defined as those with a total Customer Lifetime Value (CLV) over $100. This requires joining tables (which we'll cover next), but the critical step is filtering the aggregated SUM(purchase_value).

This is a fantastic example of how HAVING is used in practice to segment data and answer specific, high-impact business questions.

For more practice, the learnsql.com article you looked at earlier contains several excellent, real-world exercises. I encourage you to review them to solidify your understanding.

For Your Interview Prep

Since your goal is to transition to a BA role and pass interviews, it's worth noting that questions involving HAVING are very common. They test your understanding of aggregation and your ability to distinguish between row-level and group-level filtering.

The website Interview Query, which is dedicated to technical interview questions, includes many scenarios that require HAVING. For example, a beginner-level question asks to "Identify users with fewer than 3 orders OR total spend under $500." This requires a HAVING clause with COUNT and SUM connected by an OR. You can see this as question 17 in the "Beginner Level" section of the resource linked in the footnote.^[1]

^[1] You can find this and other examples in the resource SQL Scenario Based Interview Questions and Answers... (resource_id LINK).

Conclusion

Great job today! You've added a critical and powerful tool to your SQL toolkit. The HAVING clause elevates your queries from simple summarization to sophisticated, targeted analysis. You are no longer just reporting what's there; you are now able to filter those summaries to highlight the most important insights for your stakeholders.

Key Takeaways:

• The HAVING clause is used to filter query results based on aggregate functions.
• It is processed after the GROUP BY clause, so it operates on grouped data.
• The WHERE clause filters individual rows before grouping, while HAVING filters entire groups after grouping.
• The standard order of clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Memorizing this order will help you avoid syntax errors.

Preview of the Next Lesson:
So far, all our queries have been on a single table. However, in any real-world business, data is strategically separated into multiple tables to stay organized (e.g., a table for customers, another for orders, and another for products). In our next lesson, we will cover one of the most fundamental concepts in relational databases: writing queries using INNER JOIN to combine data from multiple tables. This will open up a new world of analytical possibilities.