HAVING - Filtering Groups

Introduction

You know how to group data with GROUP BY and filter rows with WHERE. But what if you want to filter groups based on their aggregate values?

• "Customers who placed more than 5 orders"
• "Products with average rating above 4.5"
• "Categories with total revenue over $10,000"

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. This lesson teaches you how to use HAVING to filter aggregated data.

WHERE vs HAVING

WHERE: Filter Rows Before Grouping

-- Orders over $100, then count per customer
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
WHERE total > 100  -- Filter individual orders
GROUP BY customer_id;

Execution:

1. Filter orders (total > 100)
2. Group by customer_id
3. Count rows in each group

HAVING: Filter Groups After Aggregation

-- Customers with more than 5 orders
SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;  -- Filter groups

Execution:

1. Group by customer_id
2. Count rows in each group
3. Keep only groups where COUNT(*) > 5

Side-by-Side Comparison

-- WHERE: "Orders over $100 per customer"
SELECT customer_id, COUNT(*)
FROM orders
WHERE total > 100  -- Row-level filter
GROUP BY customer_id;

-- HAVING: "Customers with more than 5 orders"
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;  -- Group-level filter

HAVING Syntax

SELECT
    column1,
    AGGREGATE_FUNCTION(column2) AS alias
FROM table
GROUP BY column1
HAVING AGGREGATE_FUNCTION(column2) condition;

Basic HAVING Example

-- Categories with more than 10 products
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY product_count DESC;

Result:

   category   | product_count
--------------+---------------
 Clothing     |      120
 Electronics  |       45
 Books        |       78

Categories with ≤10 products are excluded.

HAVING with Different Aggregates

HAVING with COUNT

-- Customers who placed at least 3 orders
SELECT
    c.name,
    COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) >= 3
ORDER BY order_count DESC;

HAVING with SUM

-- Customers who spent more than $1000
SELECT
    c.name,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING SUM(o.total) > 1000
ORDER BY total_spent DESC;

HAVING with AVG

-- Products with average rating above 4.5
SELECT
    p.name,
    AVG(r.rating) AS avg_rating,
    COUNT(r.id) AS review_count
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING AVG(r.rating) >= 4.5
ORDER BY avg_rating DESC;

HAVING with MIN/MAX

-- Customers whose smallest order was over $50
SELECT
    c.name,
    MIN(o.total) AS smallest_order,
    COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING MIN(o.total) > 50
ORDER BY smallest_order DESC;

Combining WHERE and HAVING

Filter Rows, Then Filter Groups

-- Customers who placed more than 3 orders over $100 in 2024
SELECT
    c.name,
    COUNT(o.id) AS large_order_count,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100                       -- Row filter
  AND EXTRACT(YEAR FROM o.order_date) = 2024
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 3                    -- Group filter
ORDER BY total_spent DESC;

Execution order:

1. FROM/JOIN: Combine tables
2. WHERE: Filter individual orders (total > 100, year = 2024)
3. GROUP BY: Group by customer
4. HAVING: Keep groups with COUNT > 3
5. SELECT: Calculate aggregates
6. ORDER BY: Sort results

Performance Tip: Use WHERE When Possible

-- ✅ Better: Filter with WHERE (before grouping)
SELECT category, COUNT(*)
FROM products
WHERE active = TRUE  -- Filter early
GROUP BY category
HAVING COUNT(*) > 10;

-- ❌ Slower: Filter with HAVING (after grouping)
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) > 10;

Rule: Use WHERE for row-level filters, HAVING only for aggregate filters.

Multiple HAVING Conditions

AND Condition

-- Categories with 10+ products AND average price > $50
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 10
   AND AVG(price) > 50
ORDER BY avg_price DESC;

OR Condition

-- Categories with either 100+ products OR average price > $200
SELECT
    category,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 100
    OR AVG(price) > 200;

Complex Conditions

-- High-value customers: 5+ orders OR total spent > $1000
SELECT
    c.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) >= 5
    OR COALESCE(SUM(o.total), 0) > 1000
ORDER BY total_spent DESC;

HAVING with Aliases

Can't Use SELECT Aliases in HAVING (in most databases)

-- ❌ Error in PostgreSQL (usually)
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING product_count > 10;  -- Error: column "product_count" does not exist

-- ✅ Correct: Repeat the aggregate
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Exception: Some databases (like MySQL) allow aliases in HAVING, but it's not standard SQL.

Workaround: Use Subquery or CTE

-- Using CTE
WITH category_counts AS (
    SELECT
        category,
        COUNT(*) AS product_count
    FROM products
    GROUP BY category
)
SELECT *
FROM category_counts
WHERE product_count > 10;  -- Now you can use the alias

Common HAVING Patterns

Pattern 1: Find Top Customers

-- Top customers (total spent > $5000)
SELECT
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING SUM(o.total) > 5000
ORDER BY total_spent DESC;

Pattern 2: Active vs Inactive Categories

-- Categories with at least 5 active products
SELECT
    category,
    COUNT(*) AS total_products,
    SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) AS active_products
FROM products
GROUP BY category
HAVING SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) >= 5;

Pattern 3: Minimum Review Threshold

-- Products with at least 10 reviews and average rating ≥ 4.0
SELECT
    p.name,
    COUNT(r.id) AS review_count,
    ROUND(AVG(r.rating), 2) AS avg_rating
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(r.id) >= 10
   AND AVG(r.rating) >= 4.0
ORDER BY avg_rating DESC, review_count DESC;

Pattern 4: Cohort Analysis

-- Monthly cohorts with at least 50 new customers
SELECT
    DATE_TRUNC('month', created_at) AS cohort_month,
    COUNT(*) AS new_customers
FROM customers
GROUP BY DATE_TRUNC('month', created_at)
HAVING COUNT(*) >= 50
ORDER BY cohort_month;

Pattern 5: Outlier Detection

-- Products with abnormally high average order quantity (> 10)
SELECT
    p.name,
    AVG(oi.quantity) AS avg_quantity_per_order,
    COUNT(oi.id) AS times_ordered
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
HAVING AVG(oi.quantity) > 10
ORDER BY avg_quantity_per_order DESC;

HAVING with NULL Values

COUNT vs COUNT(column)

-- Customers with at least 3 orders that have tracking numbers
SELECT
    c.name,
    COUNT(o.id) AS total_orders,
    COUNT(o.tracking_number) AS orders_with_tracking
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.tracking_number) >= 3;  -- Only counts non-NULL tracking numbers

COALESCE in HAVING

-- Customers who spent at least $500 (treat NULL as 0)
SELECT
    c.name,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COALESCE(SUM(o.total), 0) >= 500;

HAVING with Expressions

Percentage Filters

-- Categories where at least 80% of products are active
SELECT
    category,
    COUNT(*) AS total_products,
    SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) AS active_products,
    ROUND(100.0 * SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) / COUNT(*), 2) AS active_pct
FROM products
GROUP BY category
HAVING 100.0 * SUM(CASE WHEN active = TRUE THEN 1 ELSE 0 END) / COUNT(*) >= 80
ORDER BY active_pct DESC;

Ratio Filters

-- Products where sales to reviews ratio is at least 10:1
SELECT
    p.name,
    COUNT(DISTINCT oi.order_id) AS sales_count,
    COUNT(DISTINCT r.id) AS review_count
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT oi.order_id) >= 10 * COUNT(DISTINCT r.id)
   AND COUNT(DISTINCT r.id) > 0;  -- Avoid division by zero

When NOT to Use HAVING

Use WHERE Instead for Non-Aggregates

-- ❌ Wrong: HAVING for non-aggregate condition
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING category = 'Electronics';

-- ✅ Correct: WHERE for non-aggregate
SELECT category, COUNT(*)
FROM products
WHERE category = 'Electronics'
GROUP BY category;

Use Subquery for Complex Filters

-- Instead of complex HAVING, use subquery
WITH customer_stats AS (
    SELECT
        c.id,
        c.name,
        COUNT(o.id) AS order_count,
        SUM(o.total) AS total_spent
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id, c.name
)
SELECT *
FROM customer_stats
WHERE order_count > 5
   OR total_spent > 1000;

Performance Considerations

Index Grouped Columns

-- If you frequently filter groups by customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Speeds up this query:
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Filter Early with WHERE

-- ✅ Better: Filter with WHERE before grouping
SELECT customer_id, COUNT(*)
FROM orders
WHERE status = 'completed'  -- Reduces rows before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- ❌ Slower: Group all rows, then filter
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) > 5;

Common Mistakes

Mistake 1: Using Aggregate in WHERE

-- ❌ Error: Can't use aggregate in WHERE
SELECT customer_id, COUNT(*)
FROM orders
WHERE COUNT(*) > 5  -- Error!
GROUP BY customer_id;

-- ✅ Use HAVING
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Mistake 2: Filtering Non-Aggregates with HAVING

-- ❌ Inefficient
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING category IN ('Electronics', 'Books');

-- ✅ Use WHERE (faster)
SELECT category, COUNT(*)
FROM products
WHERE category IN ('Electronics', 'Books')
GROUP BY category;

Mistake 3: Forgetting GROUP BY

-- ❌ Error: HAVING without GROUP BY
SELECT COUNT(*)
FROM orders
HAVING COUNT(*) > 100;  -- Error: no GROUP BY

-- ✅ If no GROUP BY, use WHERE (or just check the result)
SELECT COUNT(*)
FROM orders;
-- Then check if result > 100 in your application

Practice Exercises

Exercise 1

Find categories with more than 20 products.

SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 20
ORDER BY product_count DESC;

Exercise 2

Find customers who spent more than $500 total.

SELECT
    c.name,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING SUM(o.total) > 500
ORDER BY total_spent DESC;

Exercise 3

Find products with at least 5 reviews and an average rating of 4.0 or higher.

SELECT
    p.name,
    COUNT(r.id) AS review_count,
    ROUND(AVG(r.rating), 2) AS avg_rating
FROM products p
INNER JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name
HAVING COUNT(r.id) >= 5
   AND AVG(r.rating) >= 4.0
ORDER BY avg_rating DESC;

Exercise 4

Find months in 2024 with revenue over $10,000.

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY DATE_TRUNC('month', order_date)
HAVING SUM(total) > 10000
ORDER BY month;

Exercise 5

Find customers with more than 3 orders in 2024 who spent at least $500.

SELECT
    c.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2024
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 3
   AND SUM(o.total) >= 500
ORDER BY total_spent DESC;

Key Takeaways

• ✅ HAVING filters groups after aggregation
• ✅ WHERE filters rows before grouping
• ✅ Use WHERE for non-aggregate conditions (faster)
• ✅ Use HAVING for aggregate conditions (COUNT, SUM, AVG, etc.)
• ✅ Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• ✅ Can't use SELECT aliases in HAVING (in most databases)
• ✅ Combine WHERE and HAVING for maximum efficiency
• ✅ Multiple conditions with AND/OR
• ✅ Index grouped columns for better performance

Next Steps

You've mastered aggregate functions, GROUP BY, and HAVING! In the next lesson, we'll explore Aggregates with JOINs—combining grouping with multi-table queries for powerful analytics across your entire database.

Keep building your SQL analysis skills!