Filtering Groups with Aggregated Logic

Apply constraints to grouped subsets using sophisticated HAVING clause patterns.

In this lesson, you will learn how to apply precise constraints to grouped data by using the HAVING clause to filter out subsets based on the results of aggregate functions.

The Role of HAVING

When we use a GROUP BY clause, we collapse individual rows into summary statistics—like counts, sums, or averages—for each group. While the WHERE clause filters rows before they are grouped (looking at individual data points), the HAVING clause filters the groups after the aggregation has occurred. Think of WHERE as your pre-screening filter and HAVING as your final quality control check for the summary results.

Practical Application

Imagine you are managing an online store, and you want to identify product categories that are performing exceptionally well. If you want to find categories that have generated more than $10,000 in total revenue, a simple SUM isn't enough; you must verify that the group total meets your threshold.

SELECT 
    category_id, 
    SUM(sale_amount) AS total_revenue
FROM sales
GROUP BY category_id
HAVING SUM(sale_amount) > 10000;

In this example, the database first groups the sales by category_id. It then calculates the SUM for every group. Finally, the HAVING clause inspects these totals and discards any groups that didn't hit the $10,000 mark.

Why not use WHERE?

You might wonder why we can't just use WHERE SUM(sale_amount) > 10000. The reason is architectural: the database executes the WHERE clause to decide which individual rows to pull from the table before it knows anything about the aggregate totals. By the time the GROUP BY logic runs, the data has already been filtered. The HAVING clause is specifically designed to operate on the "post-aggregation" state of your data.

You can also use complex logic within HAVING, such as HAVING COUNT(order_id) > 5 AND AVG(sale_amount) > 50, allowing you to refine your output based on multiple dimensions of group performance simultaneously.

Key Takeaway: Use the HAVING clause to filter your data based on aggregate results that are only calculated after the GROUP BY operation is complete.