Lesson 5  ·  Oct 8, 2026
Data Engineering Fundamentals

Filtering groups with HAVING

WHERE filters rows before grouping. But what if you want to filter the groups — keep only countries with at least 5 stations? WHERE can't see aggregates (WHERE COUNT(*) >= 5 is invalid SQL), because at the time WHERE runs, the groups don't exist yet. That's exactly why HAVING exists.

The code

SELECT country, COUNT(*) AS num_stations
FROM weather_stations
WHERE lon < -100.0
GROUP BY country
HAVING COUNT(*) >= 5;

The full pipeline, in execution order:

  1. WHERE — filter rows (stations west of 100°W).
  2. GROUP BY — bucket by country, count each.
  3. HAVING — keep only groups with 5+ stations.

The mnemonic: WHERE filters what goes into the groups, HAVING filters what comes out.

In practice

Any rollup that drops sparse categories — countries with fewer than N stations, products with fewer than N sales, days with fewer than N events — is GROUP BY + HAVING. Without it you'd pull the full breakdown into pandas and filter there, which is slower and noisier.

Python bridge: df[df.lon < -100].groupby('country').filter(lambda g: len(g) >= 5). Pandas' .filter() after groupby is HAVING — filtering groups, not rows.

Further reading

SQL HAVING clause — BrainStation

Try it

Practice question: using this sample of weather_stations, predict which countries survive the query above. Then change >= 5 to < 5 — what changes, and why in terms of the pipeline?

countrylon
USA-110.2
USA-105.5
USA-115.0
USA-102.3
USA-118.7
USA-108.9
Canada-120.0
Canada-125.4
Canada-110.8
Mexico-99.5
Mexico-98.2
Mexico-97.0
Mexico-99.0
Mexico-96.5
Show answer 
SELECT country, COUNT(*) AS num_stations
FROM weather_stations
WHERE lon < -100.0
GROUP BY country
HAVING COUNT(*) >= 5;
countrynum_stations
USA6

Only the USA survives — its 6 stations satisfy COUNT(*) >= 5. Canada forms a group of 3 but HAVING drops it. Mexico's 5 rows never reach grouping at all: every one fails the WHERE filter first.

SELECT country, COUNT(*) AS num_stations
FROM weather_stations
WHERE lon < -100.0
GROUP BY country
HAVING COUNT(*) < 5;
countrynum_stations
Canada3

With < 5 the complement set survives: Canada (3 < 5), USA dropped. That's HAVING re-filtering the same groups with a flipped predicate — Mexico is still gone, filtered earlier by WHERE.

← All posts