HAVING: Filter Groups

After grouping, you often want only some of the groups: "countries with more than 800 total runs", "teams with more than one player". You cannot use WHERE for this, because the totals don't exist yet when WHERE runs. That is the job of HAVING.

Definition

HAVING filters groups created by GROUP BY. It runs after grouping and aggregation, so its condition can use aggregate functions such as SUM(runs) > 800. Groups for which the condition is not TRUE are removed from the result.

Syntax

SELECT group_column, AGGREGATE(column) AS alias
FROM table_name
WHERE row_condition          -- optional: filters rows BEFORE grouping
GROUP BY group_column
HAVING group_condition;      -- filters groups AFTER grouping

Example — countries with more than 800 runs

SELECT country, SUM(runs) AS total_runs
FROM players
GROUP BY country
HAVING SUM(runs) > 800;

Execution order: 1️⃣ FROM → 2️⃣ GROUP BY country (Australia 700, England 850, India 1620) → 3️⃣ HAVING SUM(runs) > 800 removes Australia (700) → 4️⃣ SELECT.

Example — WHERE and HAVING together

SELECT country, SUM(runs) AS total_runs
FROM players
WHERE runs > 100
GROUP BY country
HAVING SUM(runs) > 800;

How it works — rows first, then groups:

1. FROM players — 6 rows.
2. WHERE runs > 100 — removes Bumrah (20) before grouping → 5 rows.
3. GROUP BY country — India now = 750 + 500 + 350 = 1600 (not 1620!), England 850, Australia 700.
4. HAVING SUM(runs) > 800 — removes Australia.
5. SELECT — shows the two remaining groups.

Example — teams with more than one player

SELECT team, COUNT(*) AS players
FROM players
GROUP BY team
HAVING COUNT(*) > 1;

WHERE vs HAVING

Tip: If a condition does not need an aggregate (e.g. country = 'India'), put it in WHERE. Removing rows early means less work for GROUP BY — faster queries.

Common mistakes

• ❌ WHERE SUM(runs) > 800 → An aggregate may not appear in the WHERE clause. ✅ HAVING SUM(runs) > 800.
• ❌ HAVING total_runs > 800 (using the SELECT alias) — SQL Server rejects it because HAVING runs before SELECT. ✅ Repeat the expression: HAVING SUM(runs) > 800.
• ❌ Putting a plain row filter in HAVING (HAVING country = 'India'). It may work, but ✅ WHERE is clearer and faster.

Interview tip

Q: Can you use WHERE and HAVING in the same query? In what order are they applied? A: Yes. WHERE is applied first to individual rows, then GROUP BY forms groups, then HAVING filters those groups. So WHERE changes what goes into the totals, while HAVING decides which totals are shown.