Filtering Groups with HAVING
In the previous guide, you learned how GROUP BY splits rows into groups and applies aggregate functions to each group separately. But what happens when you only want groups that meet a specific condition? What if you need categories with more than 2 products, customers who have spent over $100, or months with at least 3 orders?
Your first instinct might be to use WHERE, but WHERE cannot help here. It filters individual rows before grouping occurs, so it has no knowledge of aggregate results like COUNT(*) or SUM(total_amount). Trying to use WHERE with an aggregate function produces an error every time.
This is the problem HAVING solves. The HAVING clause filters groups based on aggregate conditions, working after GROUP BY has already organized and summarized the data. It is the companion to WHERE that completes your filtering toolkit: WHERE controls which rows go into the groups, and HAVING controls which groups appear in the final results.
This guide covers the critical difference between HAVING and WHERE, how to use HAVING with every aggregate function, how to combine both clauses in a single query, and common mistakes to avoid. Every example uses the ShopSmart sample database with full outputs (we defined it in a previous guide here).
The Problem HAVING Solves
Let us start by seeing the exact problem. You want to find categories that have more than 2 products. You already know how to count products per category:
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id;
Output:
| category_id | product_count |
|---|---|
| 1 | 4 |
| 2 | 2 |
| 3 | 1 |
| 4 | 3 |
Now you want only categories where product_count > 2. Your instinct says to use WHERE:
-- This FAILS
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;
Error:
ERROR: aggregate functions are not allowed in WHERE
The error is clear: aggregate functions cannot appear in WHERE. This is because WHERE executes before GROUP BY even runs. At the time WHERE is processing, the groups do not exist yet, so COUNT(*) is meaningless.
The solution is HAVING:
-- This WORKS
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;
Output:
| category_id | product_count |
|---|---|
| 1 | 4 |
| 4 | 3 |
HAVING evaluates the condition after grouping and aggregation are complete, so it can see the value of COUNT(*) for each group and decide whether to include or exclude it.
HAVING vs WHERE: The Key Difference
WHERE and HAVING both filter data, but they operate at completely different stages of query processing.
| Aspect | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups of rows |
| Executes | Before GROUP BY | After GROUP BY |
| Can use aggregate functions | No | Yes |
| Can filter regular columns | Yes | Yes (but should not) |
| Works without GROUP BY | Yes | Technically yes, but rarely useful |
The Processing Order
Understanding when each clause executes makes the difference immediately clear:
1. FROM → Identify the table(s)
2. WHERE → Filter individual rows ← Regular conditions go here
3. GROUP BY → Divide remaining rows into groups
4. HAVING → Filter groups ← Aggregate conditions go here
5. SELECT → Calculate expressions, apply aliases
6. ORDER BY → Sort results
7. LIMIT → Restrict row count
WHERE runs at step 2, before any grouping happens. It sees individual rows and can only test row-level values. HAVING runs at step 4, after groups are formed and aggregates are calculated. It sees group-level summaries and can test aggregate results.
Visual Comparison
Imagine the products table flowing through a query pipeline:
Starting data: 10 products across 4 categories
After WHERE (e.g., WHERE is_available = true):
9 products remain (Bluetooth Speaker removed)
After GROUP BY (e.g., GROUP BY category_id):
4 groups are formed, each with a count
After HAVING (e.g., HAVING COUNT(*) > 2):
2 groups remain (categories with 3+ products)
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE is_available = true -- Step 2: Remove unavailable products
GROUP BY category_id -- Step 3: Group remaining products
HAVING COUNT(*) > 2 -- Step 4: Keep only groups with 3+
ORDER BY product_count DESC; -- Step 6: Sort results
Output:
| category_id | product_count |
|---|---|
| 4 | 3 |
| 1 | 3 |
Category 1 dropped from 4 to 3 products (Bluetooth Speaker was filtered out by WHERE), but still has enough to pass the HAVING threshold. Category 2 has only 2 products and is excluded by HAVING.
Using HAVING with Aggregate Conditions
HAVING works with all aggregate functions: COUNT, SUM, AVG, MIN, and MAX. Let us explore each one.
HAVING with COUNT
The most common HAVING pattern is filtering groups by how many rows they contain:
-- Customers who have placed more than 1 order
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY order_count DESC;
Output:
| customer_id | order_count |
|---|---|
| 2 | 2 |
| 1 | 2 |
Only Alice (customer 1) and Bob (customer 2) have placed more than one order.
-- Cities with exactly 2 customers
SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
HAVING COUNT(*) = 2;
Output:
| city | customer_count |
|---|---|
| Chicago | 2 |
| New York | 2 |
-- Products ordered by more than 1 different customer
SELECT product_id, COUNT(DISTINCT order_id) AS times_ordered
FROM order_items
GROUP BY product_id
HAVING COUNT(DISTINCT order_id) > 1
ORDER BY times_ordered DESC;
Output:
| product_id | times_ordered |
|---|---|
| 10 | 2 |
| 8 | 2 |
| 4 | 2 |
| 3 | 2 |
| 2 | 2 |
These products appeared in more than one order, making them the store's most popular items.
HAVING with SUM
-- Customers who have spent more than $150 total
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 150
ORDER BY total_spent DESC;
Output:
| customer_id | total_spent |
|---|---|
| 6 | 199.98 |
| 1 | 197.47 |
| 4 | 155.00 |
| 2 | 152.99 |
-- Categories with total inventory value over $5000
SELECT c.name AS category,
ROUND(SUM(p.price * p.stock_quantity), 2) AS inventory_value
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
HAVING SUM(p.price * p.stock_quantity) > 5000
ORDER BY inventory_value DESC;
Output:
| category | inventory_value |
|---|---|
| Electronics | 20247.75 |
| Sports | 14898.20 |
Only Electronics and Sports have inventory value exceeding $5,000.
HAVING with AVG
-- Categories where the average product price exceeds $50
SELECT category_id,
ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 50
ORDER BY avg_price DESC;
Output:
| category_id | avg_price |
|---|---|
| 3 | 129.99 |
| 4 | 57.66 |
| 1 | 57.49 |
Category 2 (Books) has an average price of $38.75, which is below $50, so it is excluded.
-- Customers with an average order value above $100
SELECT customer_id,
COUNT(*) AS order_count,
ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY customer_id
HAVING AVG(total_amount) > 100
ORDER BY avg_order_value DESC;
Output:
| customer_id | order_count | avg_order_value |
|---|---|---|
| 6 | 1 | 199.98 |
| 4 | 1 | 155.00 |
| 3 | 1 | 129.99 |
HAVING with MIN and MAX
-- Categories where the cheapest product is still over $25
SELECT category_id,
MIN(price) AS cheapest,
MAX(price) AS most_expensive
FROM products
GROUP BY category_id
HAVING MIN(price) > 25
ORDER BY cheapest;
Output:
| category_id | cheapest | most_expensive |
|---|---|---|
| 1 | 29.99 | 89.99 |
| 2 | 34.99 | 42.50 |
| 3 | 129.99 | 129.99 |
Category 4 (Sports) is excluded because it includes the Stainless Water Bottle at $24.99, which is below $25.
-- Categories with a price range (difference between cheapest and most expensive) of at least $50
SELECT category_id,
MIN(price) AS cheapest,
MAX(price) AS most_expensive,
MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category_id
HAVING MAX(price) - MIN(price) >= 50
ORDER BY price_range DESC;
Output:
| category_id | cheapest | most_expensive | price_range |
|---|---|---|---|
| 4 | 24.99 | 110.00 | 85.01 |
| 1 | 29.99 | 89.99 | 60.00 |
Multiple HAVING Conditions
You can combine multiple conditions in HAVING using AND and OR, just like in WHERE:
-- Categories with more than 1 product AND average price under $60
SELECT category_id,
COUNT(*) AS product_count,
ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category_id
HAVING COUNT(*) > 1 AND AVG(price) < 60;
Output:
| category_id | product_count | avg_price |
|---|---|---|
| 1 | 4 | 57.49 |
| 2 | 2 | 38.75 |
| 4 | 3 | 57.66 |
Category 3 fails on COUNT(*) > 1 (it has only 1 product). All other categories pass both conditions.
-- Customers who have placed at least 2 orders OR spent more than $150
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2 OR SUM(total_amount) > 150
ORDER BY total_spent DESC;
Output:
| customer_id | order_count | total_spent |
|---|---|---|
| 6 | 1 | 199.98 |
| 1 | 2 | 197.47 |
| 4 | 1 | 155.00 |
| 2 | 2 | 152.99 |
Customer 6 qualifies because they spent over $150 (even with only 1 order). Customers 1 and 2 qualify because they have 2+ orders. Customer 4 qualifies because they spent over $150.
Combining WHERE and HAVING in the Same Query
The most powerful queries use both WHERE and HAVING together. WHERE pre-filters the individual rows before grouping, and HAVING post-filters the groups after aggregation.
Example: Active Product Analysis
Find categories with more than 2 available products:
SELECT category_id,
COUNT(*) AS available_products,
ROUND(AVG(price), 2) AS avg_price
FROM products
WHERE is_available = true -- Step 1: Only consider available products
GROUP BY category_id -- Step 2: Group by category
HAVING COUNT(*) > 2 -- Step 3: Keep categories with 3+ products
ORDER BY available_products DESC;
Output:
| category_id | available_products | avg_price |
|---|---|---|
| 4 | 3 | 57.66 |
| 1 | 3 | 54.99 |
Without WHERE, category 1 would have 4 products (including the unavailable Bluetooth Speaker). With WHERE is_available = true, the Bluetooth Speaker is removed before grouping, leaving category 1 with 3 available products. Both categories still pass the HAVING COUNT(*) > 2 threshold.
Example: High-Value Customer Segments
Find customers who have spent more than $100 on completed orders only:
SELECT c.first_name || ' ' || c.last_name AS customer,
COUNT(*) AS completed_orders,
SUM(o.total_amount) AS completed_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed' -- Only completed orders
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 100 -- Spent more than $100
ORDER BY completed_revenue DESC;
Output:
| customer | completed_orders | completed_revenue |
|---|---|---|
| Alice Johnson | 2 | 197.47 |
Only Alice has completed orders totaling over $100. Bob has one completed order for $89.99, which falls below the $100 threshold.
Example: Popular Products in Recent Orders
Find products ordered at least twice in orders placed during 2024 Q1 (January through March):
SELECT p.name AS product,
COUNT(*) AS times_ordered,
SUM(oi.quantity) AS total_quantity
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31' -- Q1 orders only
GROUP BY p.id, p.name
HAVING COUNT(*) >= 2 -- Ordered 2+ times
ORDER BY total_quantity DESC;
Output:
| product | times_ordered | total_quantity |
|---|---|---|
| USB-C Hub | 2 | 2 |
| SQL for Beginners | 2 | 2 |
Only USB-C Hub and SQL for Beginners appeared in 2 or more Q1 order items.
Example: Monthly Revenue Threshold
Find months with total revenue exceeding $200 from non-pending orders:
-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
WHERE status != 'pending' -- Exclude pending orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
HAVING SUM(total_amount) > 200 -- Revenue threshold
ORDER BY month;
Output:
| month | order_count | revenue |
|---|---|---|
| 2024-01 | 2 | 209.97 |
| 2024-03 | 2 | 284.99 |
January and March both exceeded $200 in non-pending revenue. February ($77.49 from one completed order) falls below the threshold. April's orders are all pending and were excluded by WHERE.
Step-by-Step Trace of WHERE + HAVING
Let us walk through a complete query to see exactly how WHERE and HAVING interact at each stage.
Query: Find categories with total stock above 100 units, counting only available products.
SELECT category_id,
COUNT(*) AS product_count,
SUM(stock_quantity) AS total_stock
FROM products
WHERE is_available = true
GROUP BY category_id
HAVING SUM(stock_quantity) > 100
ORDER BY total_stock DESC;
Step 1: FROM (all 10 products)
| id | name | category_id | price | stock_quantity | is_available |
|---|---|---|---|---|---|
| 1 | Wireless Mouse | 1 | 29.99 | 150 | true |
| 2 | Mechanical Keyboard | 1 | 89.99 | 75 | true |
| 3 | USB-C Hub | 1 | 45.00 | 200 | true |
| 4 | SQL for Beginners | 2 | 34.99 | 50 | true |
| 5 | Data Science Handbook | 2 | 42.50 | 30 | true |
| 6 | Coffee Maker Pro | 3 | 129.99 | 25 | true |
| 7 | Yoga Mat Premium | 4 | 38.00 | 100 | true |
| 8 | Running Shoes X1 | 4 | 110.00 | 60 | true |
| 9 | Bluetooth Speaker | 1 | 65.00 | 0 | false |
| 10 | Stainless Water Bottle | 4 | 24.99 | 180 | true |
Step 2: WHERE is_available = true (9 products remain, Bluetooth Speaker removed)
Step 3: GROUP BY category_id (4 groups formed)
| category_id | Products in Group | SUM(stock_quantity) |
|---|---|---|
| 1 | Wireless Mouse, Mechanical Keyboard, USB-C Hub | 425 |
| 2 | SQL for Beginners, Data Science Handbook | 80 |
| 3 | Coffee Maker Pro | 25 |
| 4 | Yoga Mat Premium, Running Shoes X1, Stainless Water Bottle | 340 |
Step 4: HAVING SUM(stock_quantity) > 100 (2 groups pass)
| category_id | product_count | total_stock | Passes HAVING? |
|---|---|---|---|
| 1 | 3 | 425 | Yes |
| 2 | 2 | 80 | No (80 ≤ 100) |
| 3 | 1 | 25 | No (25 ≤ 100) |
| 4 | 3 | 340 | Yes |
Final Output:
| category_id | product_count | total_stock |
|---|---|---|
| 1 | 3 | 425 |
| 4 | 3 | 340 |
Ask yourself this question: "Am I filtering individual rows or group summaries?"
- If the condition involves a regular column value (price, status, date, name), use
WHERE - If the condition involves an aggregate function (COUNT, SUM, AVG, MIN, MAX), use
HAVING
-- WHERE: "I only want available products" (row-level filter)
WHERE is_available = true
-- HAVING: "I only want categories with more than 2 products" (group-level filter)
HAVING COUNT(*) > 2
HAVING Without GROUP BY
Technically, you can use HAVING without GROUP BY. When you do, the entire table is treated as one group, and HAVING determines whether that single group's aggregate results are returned:
-- Does the products table have more than 5 products?
SELECT COUNT(*) AS total_products
FROM products
HAVING COUNT(*) > 5;
Output:
| total_products |
|---|
| 10 |
Since 10 > 5, the result is returned. If the table had only 3 products, this query would return an empty result set instead of a row showing 3.
-- Is the average product price above $100?
SELECT ROUND(AVG(price), 2) AS avg_price
FROM products
HAVING AVG(price) > 100;
Output:
(empty result set)
The average price is $61.05, which is not above $100, so HAVING filters out the single group and returns nothing.
While this works, it is an unusual pattern. Most HAVING usage is paired with GROUP BY.
Common HAVING Mistakes
Mistake 1: Using WHERE for Aggregate Conditions
-- Wrong: WHERE cannot use aggregate functions
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;
-- ERROR: aggregate functions are not allowed in WHERE
-- Correct: Use HAVING for aggregate conditions
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;
Mistake 2: Using HAVING for Row-Level Conditions
-- Works but inefficient: HAVING filters after grouping
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING category_id != 3;
-- Better: WHERE filters before grouping (more efficient)
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE category_id != 3
GROUP BY category_id;
Both produce the same result, but the WHERE version is better because it removes rows before they are grouped, meaning the database has less data to process during the GROUP BY step.
Always filter with WHERE when possible. Only use HAVING when the condition requires an aggregate function. Pushing filters into WHERE reduces the amount of data that needs to be grouped and aggregated, which can significantly improve performance on large datasets.
-- Inefficient: Groups ALL products, then filters groups
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING category_id IN (1, 4) AND COUNT(*) > 2;
-- Efficient: Filters products FIRST, then groups fewer rows
SELECT category_id, COUNT(*) AS product_count
FROM products
WHERE category_id IN (1, 4) -- Row-level filter → WHERE
GROUP BY category_id
HAVING COUNT(*) > 2; -- Aggregate filter → HAVING
Mistake 3: Using the Alias in HAVING
In most databases, HAVING cannot reference column aliases because it executes before SELECT:
-- Fails in PostgreSQL and SQL Server
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING product_count > 2;
-- ERROR: column "product_count" does not exist
-- Correct: Repeat the aggregate expression
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;
MySQL allows aliases in the HAVING clause:
-- Works in MySQL
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING product_count > 2;
For portability, always use the full aggregate expression in HAVING. It works in every database.
Mistake 4: Placing HAVING Before GROUP BY
-- Wrong: HAVING must come after GROUP BY
SELECT category_id, COUNT(*) AS product_count
FROM products
HAVING COUNT(*) > 2
GROUP BY category_id;
-- ERROR: syntax error
-- Correct clause order
SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;
The required clause order is always: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
Real-World HAVING Scenarios
Finding VIP Customers
Identify customers who have placed at least 2 orders and spent over $100 total:
SELECT c.first_name || ' ' || c.last_name AS customer,
c.email,
COUNT(o.id) AS total_orders,
SUM(o.total_amount) AS total_spent,
ROUND(AVG(o.total_amount), 2) AS avg_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.first_name, c.last_name, c.email
HAVING COUNT(o.id) >= 2 AND SUM(o.total_amount) > 100
ORDER BY total_spent DESC;
Output:
| customer | total_orders | total_spent | avg_order | |
|---|---|---|---|---|
| Alice Johnson | alice@email.com | 2 | 197.47 | 98.73 |
| Bob Martinez | bob@email.com | 2 | 152.99 | 76.50 |
Finding Understocked Categories
Identify categories where the average stock per product is below 50 units:
SELECT c.name AS category,
COUNT(p.id) AS product_count,
ROUND(AVG(p.stock_quantity), 0) AS avg_stock
FROM categories c
JOIN products p ON c.id = p.category_id
GROUP BY c.name
HAVING AVG(p.stock_quantity) < 50
ORDER BY avg_stock;
Output:
| category | product_count | avg_stock |
|---|---|---|
| Home & Kitchen | 1 | 25 |
| Books | 2 | 40 |
Only Home & Kitchen has an average stock below 50 units.
Identifying Best-Selling Products
Find products that generated over $50 in total revenue:
SELECT p.name AS product,
p.price AS unit_price,
SUM(oi.quantity) AS units_sold,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name, p.price
HAVING SUM(oi.quantity * oi.unit_price) > 50
ORDER BY total_revenue DESC;
Output:
| product | unit_price | units_sold | total_revenue |
|---|---|---|---|
| Running Shoes X1 | 110.00 | 2 | 220.00 |
| Mechanical Keyboard | 89.99 | 2 | 179.98 |
| Coffee Maker Pro | 129.99 | 1 | 129.99 |
| USB-C Hub | 45.00 | 2 | 90.00 |
| SQL for Beginners | 34.99 | 2 | 69.98 |
| Wireless Mouse | 29.99 | 2 | 59.98 |
Monthly Performance Report
Find months where completed and shipped orders exceeded $200 in revenue with at least 2 orders:
-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue,
ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
HAVING SUM(total_amount) > 200 AND COUNT(*) >= 2
ORDER BY month;
Output:
| month | order_count | revenue | avg_order_value |
|---|---|---|---|
| 2024-01 | 2 | 209.97 | 104.99 |
| 2024-03 | 2 | 284.99 | 142.50 |
Outlier Detection
Find categories where the price range (max minus min) exceeds $50, suggesting inconsistent pricing:
SELECT c.name AS category,
MIN(p.price) AS min_price,
MAX(p.price) AS max_price,
MAX(p.price) - MIN(p.price) AS price_spread,
COUNT(p.id) AS product_count
FROM products p
JOIN categories c ON p.category_id = c.id
GROUP BY c.name
HAVING MAX(p.price) - MIN(p.price) > 50
ORDER BY price_spread DESC;
Output:
| category | min_price | max_price | price_spread | product_count |
|---|---|---|---|---|
| Sports | 24.99 | 110.00 | 85.01 | 3 |
| Electronics | 29.99 | 89.99 | 60.00 | 4 |
The Complete Query Structure
With HAVING added to your toolkit, here is the complete query structure showing every clause you have learned so far, in the correct order:
SELECT columns, aggregate_functions
FROM table
JOIN other_table ON condition -- optional
WHERE row_level_conditions -- optional: filter rows
GROUP BY grouping_columns -- required with aggregates (usually)
HAVING aggregate_conditions -- optional: filter groups
ORDER BY columns_or_aggregates -- optional: sort results
LIMIT number -- optional: restrict row count
OFFSET number; -- optional: skip rows
Here is a comprehensive example using every clause:
SELECT c.name AS category,
COUNT(p.id) AS product_count,
ROUND(AVG(p.price), 2) AS avg_price,
SUM(p.stock_quantity) AS total_stock
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.is_available = true
GROUP BY c.name
HAVING COUNT(p.id) >= 2
ORDER BY avg_price DESC
LIMIT 3;
Output:
| category | product_count | avg_price | total_stock |
|---|---|---|---|
| Sports | 3 | 57.66 | 340 |
| Electronics | 3 | 54.99 | 425 |
| Books | 2 | 38.75 | 80 |
This single query joins two tables, filters for available products, groups by category, excludes categories with fewer than 2 products, sorts by average price descending, and limits the output to the top 3 results.
Practical Exercises
Exercise 1
Find order statuses that have more than 2 orders.
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
HAVING COUNT(*) > 2;
Expected output:
| status | order_count |
|---|---|
| completed | 3 |
| pending | 3 |
Exercise 2
Find customers who have spent more than $100 in total. Show their name and total spending.
SELECT c.first_name || ' ' || c.last_name AS customer,
SUM(o.total_amount) AS total_spent
FROM orders o
JOIN customers c ON o.customer_id = c.id
GROUP BY c.id, c.first_name, c.last_name
HAVING SUM(o.total_amount) > 100
ORDER BY total_spent DESC;
Expected output:
| customer | total_spent |
|---|---|
| Frank Wilson | 199.98 |
| Alice Johnson | 197.47 |
| David Chen | 155.00 |
| Bob Martinez | 152.99 |
| Carol Singh | 129.99 |
Exercise 3
Find categories where the average price of available products is above $40. Show the category name, product count, and average price.
SELECT c.name AS category,
COUNT(p.id) AS product_count,
ROUND(AVG(p.price), 2) AS avg_price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.is_available = true
GROUP BY c.name
HAVING AVG(p.price) > 40
ORDER BY avg_price DESC;
Expected output:
| category | product_count | avg_price |
|---|---|---|
| Home & Kitchen | 1 | 129.99 |
| Sports | 3 | 57.66 |
| Electronics | 3 | 54.99 |
Exercise 4
Find products that have been ordered more than once, showing the product name, times ordered, and total units sold.
SELECT p.name AS product,
COUNT(oi.id) AS times_ordered,
SUM(oi.quantity) AS total_units
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.name
HAVING COUNT(oi.id) > 1
ORDER BY total_units DESC;
Expected output:
| product | times_ordered | total_units |
|---|---|---|
| Wireless Mouse | 1 | 2 |
| Mechanical Keyboard | 2 | 2 |
| USB-C Hub | 2 | 2 |
| SQL for Beginners | 2 | 2 |
| Running Shoes X1 | 2 | 2 |
| Stainless Water Bottle | 2 | 2 |
Exercise 5
Find months in 2024 where the number of orders exceeded 1 AND total revenue was above $100. Use only completed and shipped orders.
-- PostgreSQL syntax
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE status IN ('completed', 'shipped')
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
HAVING COUNT(*) > 1 AND SUM(total_amount) > 100
ORDER BY month;
Expected output:
| month | orders | revenue |
|---|---|---|
| 2024-01 | 2 | 209.97 |
| 2024-03 | 2 | 284.99 |
Key Takeaways
HAVING completes your analytical query toolkit by adding the ability to filter groups after aggregation. Here is what you should remember:
HAVINGfilters groups based on aggregate conditions, whileWHEREfilters individual rowsWHEREexecutes beforeGROUP BY.HAVINGexecutes afterGROUP BY- Use
WHEREfor conditions on regular columns (price, status, date) - Use
HAVINGfor conditions on aggregate results (COUNT, SUM, AVG, MIN, MAX) HAVINGsupports all comparison operators (=,!=,<,>,<=,>=) and logical operators (AND,OR,NOT)- Always prefer
WHEREoverHAVINGfor non-aggregate conditions. It is more efficient because it reduces data before grouping - In most databases,
HAVINGcannot reference column aliases. Repeat the full aggregate expression - The clause order is:
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT - Combining
WHEREandHAVINGin the same query gives you two-stage filtering: pre-filter rows, then post-filter groups HAVINGwithoutGROUP BYtreats the entire table as one group (valid but uncommon)
With HAVING in your toolkit alongside GROUP BY, aggregate functions, WHERE, ORDER BY, and LIMIT, you now have the complete set of tools needed to write sophisticated analytical queries that answer real business questions.