Row filtering versus group filtering

WHERE filters individual rows before grouping. HAVING filters groups after the totals are worked out.

This lesson belongs to SQL reasoning in a fictional database. It builds on using aggregation and filtering.

What are we working with?

Use this original Sales table, with amounts in RM.

Worked example: one query, step by step

SELECT Branch, SUM(Amount)
FROM Sales
WHERE Amount >= 20
GROUP BY Branch
HAVING SUM(Amount) >= 73;

Step 1: WHERE removes rows. Keep rows with Amount of at least 20. These are sale 2 (Ampang, 45), sale 3 (Ampang, 30), sale 4 (Bangi, 50) and sale 6 (Bangi, 22). Four rows remain, and all Cheras rows are gone.

Step 2: GROUP BY forms groups. Ampang has 45 and 30. Bangi has 50 and 22.

Step 3: SUM totals each group. Ampang: 45 + 30 = 75. Bangi: 50 + 22 = 72.

Step 4: HAVING removes groups. Keep groups with a total of at least 73. Ampang (75) stays, and Bangi (72) is removed.

Output.

What changes without the WHERE line?

Remove WHERE Amount >= 20 and run the same query.

Now two branches appear, with bigger totals. The small sales that WHERE removed were still counted.

The mistake that costs marks

Students write WHERE SUM(Amount) >= 73. The system rejects it, because SUM cannot exist before grouping.

The fix is to ask what the condition is about. A condition on one row’s column goes in WHERE. A condition on a total, count or average goes in HAVING.

Check yourself

Using the Sales table, what does this query return?

SELECT Branch, COUNT(*)
FROM Sales
WHERE Item = 'Pen'
GROUP BY Branch
HAVING COUNT(*) >= 1;

What to study next

Groups can also be empty or contain missing values. Continue with testing an aggregate query with empty groups and missing values, then revisit using aggregation and filtering.

If you want a teacher to trace queries with you, see online one-to-one Computer Science tuition.