Introduction to Aggregate Scoping

In this lesson, you will learn how to distinguish between filtering individual records and applying constraints to entire groups of data.

When you use an aggregate function like COUNT() or SUM(), SQL stops looking at one row at a time and starts looking at a "bucket" of related data. This creates a conflict: if you want to filter your results, do you want to remove individual rows before they are grouped, or do you want to remove entire groups after the calculation is finished?

The Two Levels of Scoping

To handle this, SQL provides two different "scopes" for your filters:

1. Record-Level Filtering (WHERE): This happens before any grouping occurs. Use this to select which individual rows should be included in your analysis. For example, if you want to count customers in a specific city, you use WHERE city = 'New York' to discard rows from other cities before the grouping starts.
2. Dataset-Level Grouping Constraints (HAVING): This happens after the grouping is complete. Use this when you want to filter the resulting "buckets" based on an aggregate calculation.

A Concrete Illustration

Imagine you are analyzing sales data and want to find which product categories have sold more than 100 total units.

SELECT category, SUM(units_sold)
FROM sales
WHERE region = 'North'           -- Step 1: Filter individual rows first
GROUP BY category                -- Step 2: Group the remaining rows
HAVING SUM(units_sold) > 100;    -- Step 3: Filter the resulting groups

In this query, the WHERE clause acts as a gatekeeper for raw data. It ensures we only look at 'North' sales. The GROUP BY organizes those records into buckets (Electronics, Furniture, etc.). Finally, the HAVING clause acts as a gatekeeper for the summary results, throwing away any categories that didn't meet our total sales threshold.

If you try to use WHERE to filter for the sum, SQL will return an error because it doesn't know what the "sum" is until it has finished grouping all those individual rows.

Key Takeaway: Use WHERE to restrict the rows in your source data, and HAVING to restrict the final groups based on aggregate calculations.