Lesson 26 of 61

5 mins read

Python SQL HAVING: Filtering Aggregated Groups vs WHERE

In Plain English

The `HAVING` clause filters aggregated groups produced by `GROUP BY`. While `WHERE` filters individual rows BEFORE aggregation occurs, `HAVING` filters group metrics (e.g. `HAVING COUNT(*) > 5` or `HAVING AVG(salary) > 80000`) AFTER grouping has completed.

The Mental Model

"`WHERE` is filtering individual applicant resumes before calculating team averages; `HAVING` is filtering entire teams after looking at the team average score."

Deep Dive: How It Works

WHERE vs HAVING: `WHERE` filters raw table rows (cannot use aggregates like `WHERE AVG(x) > 10`); `HAVING` filters group calculation results (`HAVING AVG(x) > 10`).

Using Both Together: A single query can use BOTH: `WHERE status = 'active' GROUP BY department HAVING COUNT(*) >= 3`.

Performance Best Practice: Always filter non-aggregated rows in WHERE first to minimize the row volume fed into GROUP BY.

Syntax Blueprint

SELECT department, COUNT(*) AS total_employees
FROM staff
WHERE active = 1          -- Row-level pre-filter
GROUP BY department
HAVING COUNT(*) >= 5      -- Group-level post-filter
ORDER BY total_employees DESC;

WHERE filters rows before grouping; HAVING filters aggregated groups.

Core Rules to Remember

Aggregate Filtering: HAVING is the only clause allowed to filter on aggregate functions (SUM, AVG, COUNT, etc.).

WHERE Runs First: Filter individual rows in WHERE to speed up query execution before GROUP BY runs.

HAVING without GROUP BY: HAVING without GROUP BY evaluates across the single whole-table aggregate.

Common Beginner Traps & How to Fix Them

Mistake:Putting aggregate conditions inside the WHERE clause (`WHERE COUNT(*) > 1`).

Why it happens: Syntax error: WHERE evaluates before aggregate functions exist.

How to fix: Move aggregate conditions into the `HAVING` clause.

Pro Tip: Never put non-aggregated column filters into HAVING if they can be placed in WHERE; filtering in WHERE uses indexes and reduces memory.

Live Interactive Example

Hit Run Code to see it live

Filtering Departments by Headcount and Average Spend with HAVING

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: WHERE filtered cancelled orders, GROUP BY bucketed by customer, and HAVING filtered to high-volume buyers.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter Groups with HAVING

Select `department`, `COUNT(*) AS count` from `employees` grouped by `department HAVING COUNT(*) > 1`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.