Filtering Post-Aggregation Results

Filtering Post-Aggregation Results

In this lesson, you will learn how to narrow down your grouped data to only show categories that meet specific calculation criteria.

The Need for HAVING

Up until now, you have learned how to use GROUP BY to summarize data (like finding the total sales per store). But what if you only want to see stores that have a total sale amount greater than $10,000?

If you try to use a standard WHERE clause, the database will get confused because WHERE filters rows before they are grouped. Since the database hasn't finished calculating the totals yet, it doesn't know which stores qualify. To filter results after the math is done, we use the HAVING clause.

Putting It Into Practice

Imagine you have a table called orders. You want to find which customers have placed more than five orders. Here is how you write that query:

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

Think of the process in three steps:

1. GROUP BY: The database gathers all rows for each customer_id.
2. Aggregation: The COUNT function runs to calculate the total_orders for every group.
3. HAVING: The database looks at those finished totals and discards any groups that don’t meet the "greater than five" requirement.

Where Does It Fit?

It is helpful to remember that HAVING always comes after GROUP BY in your SQL statement. While WHERE acts as a filter for individual records before grouping begins, HAVING acts as a filter for the groups themselves after they have been calculated. By mastering this, you can move beyond simple summaries and start performing high-level analysis on your datasets, such as identifying top performers, flagging anomalies, or isolating specific trends in your business data.

Key Takeaway: Use the HAVING clause to filter grouped data because it processes your criteria after the aggregate functions (like SUM or COUNT) have finished their calculations.