Module 2: Aggregating & Grouping Data•25 min

Filtering Groups with HAVING

Progress Tracking

The WHERE Problem

You know WHERE filters rows. But what if you want to filter based on an aggregate result? “Show me departments with more than 3 employees” or “Find customers who’ve spent over $1000 total.”

WHERE can’t do this. It runs before GROUP BY, so the aggregates don’t exist yet:

SQL

-- This fails: aggregates aren’t allowed in WHERE
SELECT
  department,
  COUNT(*) AS emp_count
FROM techcorp_workforce
WHERE COUNT(*) > 3
GROUP BY department;

SQL will reject this query. WHERE runs before any grouping happens, so COUNT(*) doesn’t exist yet.

HAVING: The Solution

HAVING is like WHERE, but for aggregated results. It runs after GROUP BY:

SQL

-- This works
SELECT
  department,
  COUNT(*) AS emp_count
FROM techcorp_workforce
GROUP BY department
HAVING COUNT(*) > 3;

Now you only see departments with more than 3 employees. The filtering happens after the counting.

WHERE vs HAVING

Here’s the key distinction:

WHERE filters individual rows before grouping
HAVING filters groups after aggregation

You can use both in the same query, and often should.

Table: techcorp_workforce

id	first_name	last_name	department	salary	phone_number	joining_date
1	Sarah	Mitchell	HR	95000	555-0101	2021-03-15
2	Michael	Chen	HR	88000	555-0102	2022-06-01
3	Emily	Rodriguez	HR	82500		2021-09-20
4	David	Park	HR	80000	555-0104	2023-01-10
5	Lisa	Thompson	HR	65000		2021-04-05

Tables: techcorp_workforce

This finds departments where recent hires have an average salary over 80K. WHERE narrows down who’s included, HAVING narrows down which groups you see.

The Execution Order

Understanding when each clause runs helps clarify this:

FROM: Get the table
WHERE: Filter individual rows
GROUP BY: Create groups from remaining rows
HAVING: Filter groups
SELECT: Calculate and choose columns
ORDER BY: Sort the results

Common interview question

“What’s the difference between WHERE and HAVING?” Now you know.

The Alias Trap

A common gotcha: in most SQL dialects, you can’t use column aliases in HAVING.

SQL

-- This fails in most databases
SELECT
  department,
  COUNT(*) AS emp_count
FROM techcorp_workforce
GROUP BY department
HAVING emp_count > 3; -- Can’t use alias here!

-- Repeat the aggregate instead
SELECT
  department,
  COUNT(*) AS emp_count
FROM techcorp_workforce
GROUP BY department
HAVING COUNT(*) > 3; -- Use the actual function

Why? Because HAVING runs before SELECT, so the alias doesn’t exist yet. You have to repeat the aggregate function.

MySQL exception

MySQL allows aliases in HAVING. But for portability, always use the full aggregate expression.

Table: facebook_web_log

user_id	timestamp	action
0	2019-04-25 13:30:15	page_load
0	2019-04-25 13:30:18	page_load
0	2019-04-25 13:30:40	scroll_down
0	2019-04-25 13:30:45	scroll_up
0	2019-04-25 13:31:10	scroll_down

Tables: facebook_web_log

Common HAVING Patterns

Minimum Threshold

SQL

-- Only show categories with significant volume
SELECT
  category,
  COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) >= 10;

Multiple Conditions

You can combine conditions in HAVING with AND, just like WHERE.

Tables: techcorp_workforce

Departments With 5 Employees

Use HAVING COUNT(*) to filter groups by member count.

Table: employee

id	first_name	last_name	age	sex	employee_title	department	salary	target	bonus	email	city	address	manager_id
5	Max	George	26	M	Sales	Sales	1300	200	150	Max@company.com	California	2638 Richards Avenue	1
13	Katty	Bond	56	F	Manager	Management	150000	0	300	Katty@company.com	Arizona		1
11	Richerd	Gear	57	M	Manager	Management	250000	0	300	Richerd@company.com	Alabama		1
10	Jennifer	Dion	34	F	Sales	Sales	1000	200	150	Jennifer@company.com	Alabama		13
19	George	Joe	50	M	Manager	Management	100000	0	300	George@company.com	Florida	1003 Wyatt Street	1

Tables: employee

Key Takeaways

WHERE filters rows before GROUP BY
HAVING filters groups after aggregation
HAVING can use aggregate functions; WHERE cannot
You can’t use column aliases in HAVING (in most dialects)
Use both together: WHERE for row-level, HAVING for group-level filtering
Remember the execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

What’s Next

You’ve seen that aggregates ignore NULLs. But there’s more nuance here than you might expect, and getting it wrong leads to incorrect results. Next, we’ll dig into exactly how NULLs behave in aggregations.

Next upContinue →

NULL Handling in Aggregations

25 min