Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions

Introduction

You've run your first GROUP BY query. You've counted orders per customer, summed revenue by region, and averaged ratings by product. The data is grouped, the aggregates are calculated — but now you need to filter that result down. You don't want all customers; you want the ones who've placed more than five orders. You don't want all regions; you want the ones where revenue exceeded $100,000. You try adding a WHERE clause — and the database throws an error back at you.

This is the moment every SQL learner hits, and it's the exact moment HAVING becomes your new best tool. The HAVING clause exists specifically to filter the results of grouped, aggregated data. It runs after grouping happens, which makes it capable of things WHERE simply cannot do. Understanding why that distinction matters — not just what the syntax looks like — is what separates SQL writers who struggle from SQL writers who can answer real business questions fluently.

By the end of this lesson, you'll be able to write HAVING clauses confidently, know exactly when to use HAVING vs. WHERE, and build queries that answer the kinds of questions that come up every day in business analytics.

What you'll learn:

• Why WHERE cannot filter on aggregated values and what HAVING does differently
• The correct order of SQL clauses and how the database executes them
• How to write HAVING conditions using COUNT, SUM, AVG, MIN, and MAX
• When to combine WHERE and HAVING in the same query
• How to apply HAVING to real business questions about customers, sales, and performance

---

Prerequisites

You should be comfortable writing basic SELECT statements with WHERE and GROUP BY. If you need a refresher on how grouping and aggregation work from the ground up, read Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners before continuing. You should also be familiar with filtering rows — the lesson on Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization has everything you need.

---

Why WHERE Can't Filter Aggregates

Before we look at HAVING, we need to understand why it exists. The answer lives in how SQL actually executes a query.

When you write a query, the clauses don't run in the order you type them. The database processes them in a logical sequence that looks like this:

1. FROM — identify the table(s) to read
2. JOIN — combine tables if needed
3. WHERE — filter individual rows before grouping
4. GROUP BY — organize remaining rows into groups
5. HAVING — filter groups based on aggregate results
6. SELECT — choose and compute what to return
7. ORDER BY — sort the final output

Notice where WHERE sits: it runs on raw rows, before grouping. At that point in execution, there are no groups yet. There is no COUNT(*) to compare against. When you write WHERE COUNT(*) > 5, the database is being asked to filter rows using a value that hasn't been computed yet. That's the error.

HAVING runs at step five — after the groups exist and the aggregates have been calculated. That's why it can do what WHERE cannot.

Let's see this play out with a concrete example. Suppose you have an orders table:

-- orders table
-- order_id | customer_id | order_date  | total_amount
-- 1001     | 42          | 2024-01-15  | 89.99
-- 1002     | 17          | 2024-01-16  | 210.00
-- 1003     | 42          | 2024-01-18  | 45.50
-- ...

You want customers who've placed more than three orders. Here's the wrong approach:

-- This will fail with an error
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 3   -- Error: aggregate functions not allowed in WHERE
GROUP BY customer_id;

And here's the correct approach:

-- This works perfectly
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 3;

The structure is simple: write your GROUP BY query first, then add HAVING with your aggregate condition at the end.

---

The Basic HAVING Syntax

The HAVING clause always comes after GROUP BY and before ORDER BY. Its structure mirrors WHERE — you write a condition that evaluates to true or false, and only groups where the condition is true appear in your results.

SELECT column, aggregate_function(column)
FROM table
GROUP BY column
HAVING aggregate_function(column) operator value;

You can use any comparison operator you'd use in a WHERE clause: =, !=, <, >, <=, >=, BETWEEN, IN, NOT IN, and even LIKE (though that's rare in HAVING).

Here's a clean, practical example. A product manager wants to know which product categories have an average customer rating below 3.5 — a signal that something needs attention:

SELECT 
    category,
    AVG(rating) AS avg_rating,
    COUNT(*) AS review_count
FROM product_reviews
GROUP BY category
HAVING AVG(rating) < 3.5
ORDER BY avg_rating ASC;

This query groups all reviews by category, computes the average rating per category, and then discards any category where that average is 3.5 or above. The ORDER BY at the end sorts the concerning categories from worst to least bad.

---

HAVING With Multiple Conditions

Just like WHERE, you can chain multiple conditions in a HAVING clause using AND and OR.

Imagine a sales operations team asking: "Which sales reps had more than 20 deals last quarter, but an average deal size below $5,000?" That's two aggregate conditions at once:

SELECT 
    rep_id,
    COUNT(*) AS deals_closed,
    AVG(deal_amount) AS avg_deal_size,
    SUM(deal_amount) AS total_revenue
FROM deals
WHERE close_date BETWEEN '2024-07-01' AND '2024-09-30'
GROUP BY rep_id
HAVING COUNT(*) > 20
   AND AVG(deal_amount) < 5000
ORDER BY avg_deal_size ASC;

Notice something important here: this query uses both WHERE and HAVING. The WHERE clause filters rows to only the ones from last quarter before grouping. The HAVING clause then filters the resulting groups. This is correct — and common.

---

WHERE and HAVING Working Together

The combination of WHERE and HAVING is where your queries start answering genuinely nuanced business questions. Think of them as two separate gates: WHERE thins out the raw data before it gets grouped, and HAVING filters the resulting summaries.

Let's build a realistic scenario. A subscription business wants to identify customers who've been active for at least a year and have made more than ten purchases — potential candidates for a VIP loyalty program:

SELECT 
    customer_id,
    COUNT(order_id) AS total_orders,
    SUM(order_total) AS lifetime_value,
    MIN(order_date) AS first_order_date
FROM orders
WHERE order_date >= '2023-01-01'       -- Only consider orders from 2023 onward
  AND order_status = 'completed'        -- Only count completed orders
GROUP BY customer_id
HAVING COUNT(order_id) > 10
   AND SUM(order_total) > 500
ORDER BY lifetime_value DESC;

Walk through the execution mentally:

1. The database reads the orders table
2. WHERE removes any rows before 2023 or with a non-completed status
3. The remaining rows are grouped by customer_id
4. COUNT and SUM are calculated per group
5. HAVING removes any customer groups that don't meet both thresholds
6. The final list is sorted by lifetime value, highest first

This is a query that a real analyst would build to feed a marketing campaign. It's readable, logical, and correct.

---

HAVING With COUNT: Finding Active (or Inactive) Groups

COUNT is probably the most common aggregate you'll use in HAVING clauses, because many business questions are really about volume thresholds.

Finding categories with enough data to be statistically meaningful:

SELECT 
    product_category,
    AVG(return_rate) AS avg_return_rate
FROM product_sales
GROUP BY product_category
HAVING COUNT(*) >= 100   -- Only show categories with enough sales to trust the rate
ORDER BY avg_return_rate DESC;

This pattern — filtering by COUNT to ensure a minimum sample size before trusting an average — is extremely practical. An average return rate calculated from three sales is meaningless. One calculated from a thousand sales tells you something real.

Finding customers who've never placed more than one order:

SELECT 
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1;

This surfaces one-time buyers — a segment that a retention team might want to target with a "we miss you" campaign.

---

HAVING With SUM: Revenue and Volume Thresholds

SUM in a HAVING clause lets you filter by totals — total revenue, total quantity, total cost — rather than individual row values.

A common warehouse management question: which products have had more than 1,000 units sold in the current year?

SELECT 
    product_id,
    product_name,
    SUM(quantity_sold) AS total_units,
    SUM(quantity_sold * unit_price) AS total_revenue
FROM sales_line_items
WHERE sale_year = 2024
GROUP BY product_id, product_name
HAVING SUM(quantity_sold) > 1000
ORDER BY total_units DESC;

Or, flipping the direction, which suppliers have shipped fewer than 500 total units this quarter — perhaps signaling a supply issue?

SELECT 
    supplier_id,
    SUM(units_shipped) AS total_shipped
FROM shipments
WHERE shipment_date >= '2024-10-01'
GROUP BY supplier_id
HAVING SUM(units_shipped) < 500;

---

HAVING With AVG, MIN, and MAX: Performance Benchmarks

AVG, MIN, and MAX unlock a different class of questions — ones about performance benchmarks, outliers, and quality thresholds.

Finding warehouse regions where average delivery time is too slow:

SELECT 
    warehouse_region,
    AVG(delivery_days) AS avg_delivery,
    MAX(delivery_days) AS worst_delivery
FROM shipments
WHERE shipment_date >= '2024-01-01'
GROUP BY warehouse_region
HAVING AVG(delivery_days) > 5
ORDER BY avg_delivery DESC;

Finding employees whose best-ever deal was still below the company minimum target:

SELECT 
    employee_id,
    MAX(deal_value) AS best_deal
FROM closed_deals
GROUP BY employee_id
HAVING MAX(deal_value) < 10000;

This query identifies anyone for whom even their single best deal fell below the $10,000 threshold — a training flag.

---

A Complete Business Scenario: Monthly Sales Reporting

Let's put everything together with a realistic multi-step problem. Your finance team wants a monthly sales summary report, but with specific criteria:

• Only include months in 2024
• Only include months where the company processed more than 200 orders
• Only include months where total revenue exceeded $50,000
• Flag months where the average order value dropped below $200 (indicating potential issues)

SELECT 
    YEAR(order_date)  AS sale_year,
    MONTH(order_date) AS sale_month,
    COUNT(*)          AS order_count,
    SUM(order_total)  AS monthly_revenue,
    AVG(order_total)  AS avg_order_value,
    MIN(order_total)  AS smallest_order,
    MAX(order_total)  AS largest_order
FROM orders
WHERE order_status = 'completed'
  AND order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 
    YEAR(order_date),
    MONTH(order_date)
HAVING COUNT(*) > 200
   AND SUM(order_total) > 50000
ORDER BY 
    sale_year,
    sale_month;

This is a query you could drop directly into a monthly reporting pipeline. The WHERE clause does the heavy lifting of excluding irrelevant data early; the HAVING clause enforces the business thresholds that make a month worth reporting on.

For further exploration of how to layer conditions and combine aggregates with logic, the article on Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice takes this pattern further with CASE WHEN inside aggregates.

---

Hands-On Exercise

Use the following table structure for these exercises. Create a simple dataset mentally or in your own SQL environment:

-- Table: employee_sales
-- columns: employee_id, department, sale_date, sale_amount, product_category

Assume this table contains two years of sales records across five departments and three product categories.

Exercise 1 — Basic HAVING: Write a query that returns each department along with its total sales amount, but only for departments where total sales exceeded $250,000.

Exercise 2 — HAVING with two conditions: Write a query showing each product category with its order count and average sale amount, but only include categories that had more than 50 sales and an average sale amount above $1,500.

Exercise 3 — WHERE and HAVING together: Write a query that looks only at sales from 2024, groups by employee, and returns employees who made more than 30 sales but whose average sale was below $800. Include their total revenue and sort by total revenue descending.

Exercise 4 — Challenge: Write a query that identifies departments where the single largest individual sale was less than $5,000. (Hint: HAVING MAX(sale_amount) < 5000.) Think about what business question this answers — what would you tell the department head?

---

Common Mistakes & Troubleshooting

Mistake 1: Using a column alias in HAVING

A very common error is trying to use the alias you defined in SELECT inside HAVING:

-- This will fail in most databases
SELECT department, SUM(sale_amount) AS total_sales
FROM employee_sales
GROUP BY department
HAVING total_sales > 100000;   -- Error: "total_sales" doesn't exist yet

Remember the execution order: HAVING runs before SELECT finalizes the aliases. You must repeat the full aggregate expression:

-- Correct
SELECT department, SUM(sale_amount) AS total_sales
FROM employee_sales
GROUP BY department
HAVING SUM(sale_amount) > 100000;

Mistake 2: Filtering in HAVING when WHERE would work

If your condition doesn't involve an aggregate, it belongs in WHERE:

-- Inefficient: filtering a non-aggregate in HAVING
SELECT department, COUNT(*)
FROM employee_sales
GROUP BY department
HAVING department = 'Engineering';  -- Wrong place for this

-- Correct: filter non-aggregate rows before grouping
SELECT department, COUNT(*)
FROM employee_sales
WHERE department = 'Engineering'
GROUP BY department;

The second version is faster because the database filters out all non-Engineering rows before doing any grouping work.

Mistake 3: Forgetting that HAVING filters groups, not rows

A HAVING condition removes entire groups from output. If HAVING COUNT(*) > 10 eliminates the "West" region, you won't see any West rows — not even a partial set. This is by design, but it catches people off guard. If you want to see all groups but just ranked by size, use ORDER BY instead.

Mistake 4: Applying HAVING without GROUP BY

HAVING technically works without GROUP BY — in that case, the entire table is treated as one group. But this is almost never what you want, and it confuses readers. If you find yourself writing HAVING without GROUP BY, double-check your logic.

For a deeper look at how HAVING fits into more advanced analytical patterns, the lesson on Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE covers edge cases and performance considerations in detail.

---

Summary & Next Steps

The HAVING clause is a focused tool that solves one specific problem: filtering aggregated groups. It runs after GROUP BY, which means it sees computed values like COUNT(*), SUM(), AVG(), MIN(), and MAX(). WHERE runs before grouping and can only see raw row data. Understanding this distinction — not just memorizing it — is what lets you write correct queries the first time.

The key patterns to carry forward:

• Use WHERE to filter raw rows before aggregation; use HAVING to filter groups after aggregation
• Combine both in the same query when you need both kinds of filtering
• Repeat the full aggregate expression in HAVING rather than using a column alias
• Think carefully about which aggregate (COUNT, SUM, AVG, MIN, MAX) actually answers your business question

Where to go next:

• To see how HAVING combines with conditional logic inside aggregate functions, read Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice
• To learn how to bring data from multiple tables together before grouping, explore Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query
• When your HAVING-filtered results become the input to a larger query, you're ready for Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements
• For the full picture of how GROUP BY and HAVING work together at scale, Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG goes deeper into performance and edge cases

HAVING is one of those SQL tools that, once you understand it properly, you'll find yourself reaching for constantly. Business questions are almost always about thresholds, benchmarks, and outliers — and those are exactly the questions HAVING is built to answer.