Part 7: Mastering the HAVING Clause | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 7 · Lecture Notes
Mastering the HAVING Clause
How to filter grouped business summaries after aggregation — applying business thresholds to GROUP BY results to surface only meaningful categories.
Part 7 of 7100% Complete
HAVING
WHERE vs HAVING
Aggregate Filtering
Business Thresholds
8 Practice Questions
Learning Objectives
Understand the difference between WHERE (filters rows) and HAVING (filters group summaries)
Use HAVING with COUNT(), SUM(), and AVG() to apply business thresholds to grouped output
Follow the correct full clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Write HAVING conditions using the full aggregate function, not the column alias
Click any section header to open or close it
HAVING filters grouped results after aggregation has been performed. WHERE filters individual rows before grouping; HAVING filters the group summaries after GROUP BY and the aggregate function have run. This distinction is critical — HAVING can reference aggregated values; WHERE cannot.
In industry, analysts use HAVING to identify high-performing categories, filter grouped summaries to only those meeting a business threshold, analyze operational thresholds across groups, isolate important customer segments, and create business performance reports that focus on meaningful results.
WHERE vs HAVING — The Fundamental Distinction
WHERE filters rows before any grouping occurs and cannot reference aggregated values like SUM(amount) or COUNT(*). HAVING filters the grouped summaries after aggregation and can reference aggregate results. If you need to filter on a grouped total, it must go in HAVING, not WHERE.
Basic HAVING Syntax
SELECT column_name, AGG_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
HAVING evaluates the result of the aggregation for each group and keeps only the groups where the HAVING condition is true. Where GROUP BY produces all groups, HAVING narrows the output to only the groups that meet the business threshold.
Reading a HAVING Query
Work through it in order: FROM identifies the table, GROUP BY forms the groups, the aggregate function calculates each group summary, then HAVING evaluates the condition and keeps only passing groups. SELECT formats the final output.
Transaction Types Where Total Exceeds 100
SELECT
transaction_type,
ROUND(SUM(amount), 2) AS total_amount
FROM transactions
GROUP BY transaction_type
HAVINGSUM(amount) > 100;
HAVING works with any aggregate function. HAVING COUNT(*) > 1 keeps only groups with more than one record. HAVING AVG(unit_price) > 50 keeps only categories with an average price above the threshold. HAVING SUM(monthly_fee) > 50 keeps only subscription groups with meaningful revenue.
Reference the Aggregate Function in HAVING, Not the Alias
Write HAVING SUM(amount) > 100, not HAVING total_amount > 100. Referencing the full aggregate function in HAVING is always the safe, portable approach across all SQL databases.
Categories With Average Price Above 50
SELECT
category,
ROUND(AVG(unit_price), 2) AS average_price
FROM products
GROUP BY category
HAVINGAVG(unit_price) > 50;
Countries With 2 or More Users
SELECT
country,
COUNT(*) AS total_users
FROM users
GROUP BY country
HAVINGCOUNT(*) >= 2;
HAVING is the tool that makes GROUP BY analytically powerful. Without HAVING, GROUP BY shows every group, including trivial ones. With HAVING, the output shows only the groups that meet a business threshold — the categories and segments that actually matter for decision-making.
Full Clause Order with HAVING
The complete order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. WHERE filters individual rows. GROUP BY forms groups. HAVING filters groups. ORDER BY sorts. LIMIT caps the output.
Subscription Groups With Meaningful Revenue
SELECT
status,
ROUND(SUM(monthly_fee), 2) AS total_fees
FROM subscriptions
GROUP BY status
HAVINGSUM(monthly_fee) > 50;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Retrieve transaction types where total transaction amounts are greater than 100 rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve transaction types where total transaction amounts are greater than 100 rounded to 2 decimal places.
SQL Answer
SELECT
transaction_type,
ROUND(SUM(amount), 2) AS total_amount
FROM transactions
GROUP BY transaction_type
HAVINGSUM(amount) > 100;
Practice 2Retrieve product categories where average unit_price is greater than 50 rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve product categories where average unit_price is greater than 50 rounded to 2 decimal places.
SQL Answer
SELECT
category,
ROUND(AVG(unit_price), 2) AS average_price
FROM products
GROUP BY category
HAVINGAVG(unit_price) > 50;
Practice 3Retrieve countries with more than 2 users
AI Prompt
Write a MySQL query to retrieve countries with more than 2 users.
SQL Answer
SELECT
country,
COUNT(*) AS total_users
FROM users
GROUP BY country
HAVINGCOUNT(*) > 2;
Practice 4Retrieve order statuses where total orders are greater than 1
AI Prompt
Write a MySQL query to retrieve order statuses where total orders are greater than 1.
SQL Answer
SELECT
order_status,
COUNT(*) AS total_orders
FROM orders
GROUP BY order_status
HAVINGCOUNT(*) > 1;
Practice 5Retrieve subscription statuses where total monthly_fee is greater than 50 rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve subscription statuses where total monthly_fee is greater than 50 rounded to 2 decimal places.
SQL Answer
SELECT
status,
ROUND(SUM(monthly_fee), 2) AS total_fees
FROM subscriptions
GROUP BY status
HAVINGSUM(monthly_fee) > 50;
Practice 6Retrieve ticket statuses where total tickets are greater than 1
AI Prompt
Write a MySQL query to retrieve ticket statuses where total tickets are greater than 1.
SQL Answer
SELECT
ticket_status,
COUNT(*) AS total_tickets
FROM support_tickets
GROUP BY ticket_status
HAVINGCOUNT(*) > 1;
Practice 7Retrieve campaign channels where total campaigns are greater than 1
AI Prompt
Write a MySQL query to retrieve campaign channels where total campaigns are greater than 1.
SQL Answer
SELECT
channel,
COUNT(*) AS total_campaigns
FROM campaigns
GROUP BY channel
HAVINGCOUNT(*) > 1;
Practice 8Retrieve device types where total sessions are greater than 1
AI Prompt
Write a MySQL query to retrieve device types where total sessions are greater than 1.
SQL Answer
SELECT
device_type,
COUNT(*) AS total_sessions
FROM sessions
GROUP BY device_type
HAVINGCOUNT(*) > 1;
Knowledge Check
Five questions — select the best answer for each.
1. What is the key difference between WHERE and HAVING?
AWHERE filters grouped results; HAVING filters individual rows
BWHERE filters individual rows before grouping; HAVING filters group summaries after aggregation
CWHERE works with aggregate functions; HAVING does not
DWHERE and HAVING do the same thing and are interchangeable
Correct. WHERE operates on individual rows before any grouping occurs and cannot reference aggregate values. HAVING operates on group summaries after aggregation and can reference COUNT(), SUM(), AVG() results.
Not quite. WHERE filters rows before grouping. HAVING filters group summaries after aggregation. They operate at different stages.
2. Where does HAVING appear in the SQL clause order?
ABefore FROM
BBefore GROUP BY
CAfter GROUP BY
DAfter ORDER BY
Correct. The full clause order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. HAVING always follows GROUP BY.
Not quite. HAVING comes after GROUP BY in the clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
3. Which condition should go in HAVING rather than WHERE?
AWHERE country = ‘Canada’
BWHERE transaction_type = ‘Purchase’
CHAVING SUM(amount) > 100
DWHERE order_status = ‘Completed’
Correct. SUM(amount) > 100 is an aggregated condition that filters group summaries. Aggregated conditions always belong in HAVING, not WHERE.
Not quite. SUM(amount) > 100 filters a group total and belongs in HAVING. WHERE conditions filter individual rows, not aggregated values.
4. An analyst writes HAVING COUNT(*) > 2 after GROUP BY country. What does this return?
AAll countries regardless of user count
BOnly countries that have more than 2 users
CThe country with exactly 3 users
DIndividual users who appear more than twice
Correct. HAVING COUNT(*) > 2 evaluates the count for each country group after GROUP BY and keeps only countries where the count exceeds 2.
Not quite. HAVING COUNT(*) > 2 keeps only the groups where the row count exceeds 2 — countries with 3 or more users.
5. Why is HAVING analytically powerful when combined with GROUP BY?
AIt makes GROUP BY run faster on large tables
BIt prevents GROUP BY from producing duplicate rows
CIt filters out trivial groups, surfacing only those that meet a meaningful business threshold
DIt sorts the grouped output automatically
Correct. Without HAVING, GROUP BY shows every group including trivial ones. HAVING focuses the output on groups that meet a meaningful business threshold, improving the quality of analytical reporting.
Not quite. HAVING filters group summaries so only groups meeting a meaningful threshold appear in the output, making reports more focused and actionable.
Part 7 Summary
The key ideas from this lecture — keep these in mind as you move forward.
HAVING helps analysts filter grouped business summaries using aggregate conditions and operational thresholds. WHERE filters rows; HAVING filters groups. HAVING always comes after GROUP BY. Full clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
HAVINGWHERE vs HAVINGGROUP BYAggregate FilterBusiness ThresholdClause OrderCOUNT()SUM()AVG()
