Part 6: Mastering the HAVING Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 6 · Lecture Notes
Mastering the HAVING Command
How to filter grouped results after aggregation — applying business thresholds to summarized data and identifying meaningful categories.
Part 6 of 6100% Complete
HAVING
WHERE vs HAVING
Aggregation Conditions
8 Practice Questions
Learning Objectives
Understand how HAVING differs from WHERE — HAVING filters groups after aggregation
Apply HAVING with COUNT(), SUM(), and AVG() to filter grouped summaries
Use HAVING to apply business thresholds to categorized data
Identify the correct full clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Click any section header to open or close it
HAVING filters grouped results after aggregation has been applied. While WHERE filters individual rows before grouping, HAVING filters the grouped summaries that GROUP BY produces. The distinction is critical: WHERE cannot reference aggregated values; HAVING can.
In industry, analysts use HAVING to identify high-value customers, isolate top-performing products, detect low-performing categories, apply business thresholds to summarized data, and filter reports to only meaningful groups.
WHERE vs HAVING — The Key Difference
WHERE filters rows before they are grouped — it cannot see aggregated values. HAVING filters groups after aggregation — it works on the summary output. If you try to use WHERE SUM(amount) > 500, the query will fail. That condition belongs in HAVING.
Basic HAVING Syntax
SELECT column_name, AGG_FUNCTION(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;
HAVING evaluates each group summary and keeps only the groups that meet the condition. Every WHERE filter condition you have learned — =, !=, >, <, BETWEEN — can also be used in HAVING, but applied to aggregated values instead of row values.
Reading a HAVING Query
Work through it in order: FROM identifies the table, GROUP BY forms the groups, the aggregation function calculates each group’s summary, then HAVING applies the filter to keep only the groups that pass. Only then does SELECT format the output.
Countries With More Than 1 User
SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country
HAVINGCOUNT(*) > 1;
HAVING is the tool that turns raw aggregated data into actionable business intelligence. By setting thresholds on grouped metrics — revenue above a level, order counts above a minimum, average price above a benchmark — analysts can identify which categories, customers, or products meet meaningful business criteria.
Common Business Thresholds
HAVING SUM(amount) > 500 — transaction types generating significant revenue. HAVING COUNT(*) > 2 — statuses with multiple records. HAVING AVG(unit_price) > 50 — categories priced above a benchmark. HAVING SUM(line_total) > 100 — products with meaningful sales volume.
Transaction Types Generating Over 500
SELECT transaction_type, ROUND(SUM(amount), 2) AS total_amount
FROM transactions
GROUP BY transaction_type
HAVINGSUM(amount) > 500;
Products With Significant Sales Volume
SELECT product_id, ROUND(SUM(line_total), 2) AS total_sales
FROM order_items
GROUP BY product_id
HAVINGSUM(line_total) > 100;
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 1Show countries with more than 1 user
AI Prompt
Write a MySQL query to count users by country and show only countries with more than 1 user.
SQL Answer
SELECT
country,
COUNT(*) AS total_users
FROM users
GROUP BY country
HAVINGCOUNT(*) > 1;
Practice 2Show transaction types with total amount greater than 500 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total transaction amount by transaction_type and show only values greater than 500 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) > 500;
Practice 3Show products with total sales greater than 100 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total sales by product_id and show only products with total sales greater than 200 rounded to 2 decimal places.
SQL Answer
SELECT
product_id,
ROUND(SUM(line_total), 2) AS total_sales
FROM order_items
GROUP BY product_id
HAVINGSUM(line_total) > 100;
Practice 4Show order statuses with more than 2 orders
AI Prompt
Write a MySQL query to count orders by order_status and show only statuses with more than 2 orders.
SQL Answer
SELECT
order_status,
COUNT(*) AS total_orders
FROM orders
GROUP BY order_status
HAVINGCOUNT(*) > 2;
Practice 5Show categories with average product price greater than 50 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate average product price by category and show only categories with average price greater than 50 rounded to 2 decimal places.
SQL Answer
SELECT
category,
ROUND(AVG(unit_price), 2) AS avg_price
FROM products
GROUP BY category
HAVINGAVG(unit_price) > 50;
Practice 6Show subscription statuses with total monthly_fee greater than 300 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total monthly_fee by status and show only totals greater than 300 rounded to 2 decimal places.
SQL Answer
SELECT
status,
ROUND(SUM(monthly_fee), 2) AS total_monthly_fee
FROM subscriptions
GROUP BY status
HAVINGSUM(monthly_fee) > 300;
Practice 7Show users with total transaction amount greater than 100 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total transaction amount by user_id and show only totals greater than 250 rounded to 2 decimal places.
SQL Answer
SELECT
user_id,
ROUND(SUM(amount), 2) AS total_amount
FROM transactions
GROUP BY user_id
HAVINGSUM(amount) > 250;
Practice 8Show ticket statuses with more than 1 support ticket
AI Prompt
Write a MySQL query to count support tickets by ticket_status and show only statuses with more than 1 ticket.
SQL Answer
SELECT
ticket_status,
COUNT(*) AS total_tickets
FROM support_tickets
GROUP BY ticket_status
HAVINGCOUNT(*) > 1;
Knowledge Check
Five questions — select the best answer for each.
1. What is the key difference between WHERE and HAVING?
AWHERE is used with GROUP BY; HAVING is used with ORDER BY
BWHERE filters rows before grouping; HAVING filters grouped summaries after aggregation
CWHERE works with numbers; HAVING works with text
DWHERE and HAVING do the same thing — they are interchangeable
Correct. WHERE filters individual rows before any grouping occurs. HAVING filters the grouped summaries that GROUP BY produces — it can reference aggregated values; WHERE cannot.
Not quite. WHERE filters rows before grouping. HAVING filters grouped summaries after aggregation. They operate at different stages of the query.
2. Which query correctly shows only countries with more than 1 user?
ASELECT country, COUNT(*) FROM users WHERE COUNT(*) > 1 GROUP BY country;
BSELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 1;
CSELECT country FROM users HAVING COUNT(*) > 1;
DSELECT country, COUNT(*) FROM users HAVING country > 1 GROUP BY country;
Correct. GROUP BY comes before HAVING. The HAVING condition references the aggregated COUNT(*), not a raw column value.
Not quite. The correct structure is: GROUP BY column HAVING AGG_FUNCTION(column) condition.
3. Where does HAVING appear in the SQL clause order?
ABefore WHERE
BAfter SELECT but before FROM
CAfter GROUP BY
DAfter LIMIT
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.
4. An analyst wants to find products where total sales exceed 200. Which clause should they use?
AWHERE SUM(line_total) > 200
BHAVING line_total > 200
CHAVING SUM(line_total) > 200
DWHERE line_total > 200 GROUP BY product_id
Correct. HAVING references the aggregated value SUM(line_total). WHERE cannot reference aggregated values — it works on individual row values before grouping.
Not quite. Aggregated conditions belong in HAVING: HAVING SUM(line_total) > 200.
5. Which HAVING condition finds transaction types where the average amount exceeds 100?
AHAVING amount > 100
BHAVING AVG(amount) > 100
CWHERE AVG(amount) > 100
DHAVING COUNT(amount) > 100
Correct. AVG(amount) calculates the average for each group. HAVING then filters to keep only those groups where that average exceeds 100.
Not quite. HAVING AVG(amount) > 100 is the correct syntax — it filters groups where the average amount exceeds the threshold.
Part 6 Summary
The key ideas from this lecture — keep these in mind as you move forward.
HAVING filters grouped business summaries so analysts can focus on meaningful categories and threshold-based insights. WHERE filters individual rows before grouping. HAVING filters group summaries after aggregation. The full clause order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
HAVINGWHERE vs HAVINGGROUP BYAggregation FilterBusiness ThresholdCOUNT()SUM()AVG()Clause Order
