Filtering Groups with HAVING | Tutorial Reference

Learn how to use the SQL HAVING clause to filter groups after aggregation, including key differences from WHERE, practical examples with COUNT, SUM, AVG, and more.

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

When to Use WHERE vs HAVING

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.

Performance Rule

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 Exception

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	email	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:

HAVING filters groups based on aggregate conditions, while WHERE filters individual rows
WHERE executes before GROUP BY. HAVING executes after GROUP BY
Use WHERE for conditions on regular columns (price, status, date)
Use HAVING for conditions on aggregate results (COUNT, SUM, AVG, MIN, MAX)
HAVING supports all comparison operators (=, !=, <, >, <=, >=) and logical operators (AND, OR, NOT)
Always prefer WHERE over HAVING for non-aggregate conditions. It is more efficient because it reduces data before grouping
In most databases, HAVING cannot reference column aliases. Repeat the full aggregate expression
The clause order is: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Combining WHERE and HAVING in the same query gives you two-stage filtering: pre-filter rows, then post-filter groups
HAVING without GROUP BY treats 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.