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:
- Filter orders (total > 100)
- Group by customer_id
- 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:
- Group by customer_id
- Count rows in each group
- 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:
- FROM/JOIN: Combine tables
- WHERE: Filter individual orders (total > 100, year = 2024)
- GROUP BY: Group by customer
- HAVING: Keep groups with COUNT > 3
- SELECT: Calculate aggregates
- 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.
Solution
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.
Solution
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.
Solution
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.
Solution
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.
Solution
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!
اختبار قصير
التالي · الدرس 24 من 44
Combining Aggregates with Joins
قراءة 8 دقيقة
أنهيت 23 من 44 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

