PostgreSQL HAVING

Overview

The HAVING clause is used to filter groups of rows after the GROUP BY clause has been applied. While WHERE filters individual rows before grouping, HAVING filters groups after aggregation.

Basic Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

WHERE vs HAVING

-- WHERE: Filters rows before grouping
SELECT category, COUNT(*) AS product_count
FROM products
WHERE price > 10  -- Filter individual rows
GROUP BY category;

-- HAVING: Filters groups after aggregation
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;  -- Filter groups

-- Both together
SELECT category, AVG(price) AS avg_price
FROM products
WHERE stock > 0  -- Filter rows first
GROUP BY category
HAVING AVG(price) > 50;  -- Then filter groups

HAVING with COUNT

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

-- Users with more than 5 orders
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- Count distinct
SELECT category, COUNT(DISTINCT brand) AS brand_count
FROM products
GROUP BY category
HAVING COUNT(DISTINCT brand) > 3;

HAVING with SUM

-- Users who spent more than $1000
SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

-- Categories with total inventory value > $10000
SELECT 
    category,
    SUM(price * stock) AS inventory_value
FROM products
GROUP BY category
HAVING SUM(price * stock) > 10000;

HAVING with AVG

-- Categories with average price > $50
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

-- Users with average order value > $100
SELECT 
    user_id,
    COUNT(*) AS order_count,
    AVG(total) AS avg_order_value
FROM orders
GROUP BY user_id
HAVING AVG(total) > 100;

HAVING with MIN/MAX

-- Categories where cheapest product costs more than $10
SELECT category, MIN(price) AS min_price
FROM products
GROUP BY category
HAVING MIN(price) > 10;

-- Categories with price range > $100
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category
HAVING MAX(price) - MIN(price) > 100;

Multiple HAVING Conditions

-- AND conditions
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5 AND AVG(price) < 100;

-- OR conditions
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 100 OR AVG(price) > 500;

-- Complex conditions
SELECT 
    category,
    COUNT(*) AS product_count,
    SUM(stock) AS total_stock,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 10 
    AND SUM(stock) > 100 
    AND AVG(price) BETWEEN 20 AND 200;

HAVING with Expressions

-- Using calculated values
SELECT 
    category,
    SUM(price * stock) AS inventory_value
FROM products
GROUP BY category
HAVING SUM(price * stock) > 50000;

-- Using CASE in HAVING
SELECT 
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(CASE WHEN price > 100 THEN 1 END) > 5;

HAVING with Subqueries

-- Compare to overall average
SELECT 
    category,
    AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > (SELECT AVG(price) FROM products);

-- Compare to specific value from subquery
SELECT 
    user_id,
    SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > (
    SELECT AVG(total_spent)
    FROM (
        SELECT user_id, SUM(total) AS total_spent
        FROM orders
        GROUP BY user_id
    ) AS user_totals
);

HAVING with JOIN

-- Filter joined and grouped data
SELECT 
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 3 AND SUM(o.total) > 500;

-- Multiple joins with HAVING
SELECT 
    c.category_name,
    COUNT(DISTINCT p.id) AS product_count,
    SUM(oi.quantity) AS total_sold
FROM categories c
INNER JOIN products p ON c.id = p.category_id
INNER JOIN order_items oi ON p.id = oi.product_id
GROUP BY c.id, c.category_name
HAVING COUNT(DISTINCT p.id) > 5 AND SUM(oi.quantity) > 100;

HAVING with Date Functions

-- Groups by month, filter by order count
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
HAVING COUNT(*) > 50;

-- Filter by date range in aggregation
SELECT 
    user_id,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;

HAVING with ROLLUP/CUBE

-- Filter rollup results
SELECT 
    category,
    brand,
    COUNT(*) AS product_count
FROM products
GROUP BY ROLLUP (category, brand)
HAVING COUNT(*) > 5;

-- Filter specific grouping levels
SELECT 
    category,
    brand,
    COUNT(*) AS product_count,
    GROUPING(category) AS is_category_total,
    GROUPING(brand) AS is_brand_total
FROM products
GROUP BY ROLLUP (category, brand)
HAVING GROUPING(category) = 0 AND COUNT(*) > 10;

Common Patterns

Top N Groups

-- Top 10 categories by product count
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 0
ORDER BY product_count DESC
LIMIT 10;

Percentage Filters

-- Categories representing more than 5% of products
SELECT 
    category,
    COUNT(*) AS product_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM products
GROUP BY category
HAVING COUNT(*) > (SELECT COUNT(*) * 0.05 FROM products);

Statistical Filters

-- Categories with high price variance
SELECT 
    category,
    AVG(price) AS avg_price,
    STDDEV(price) AS price_stddev
FROM products
GROUP BY category
HAVING STDDEV(price) > 50;

Performance Tips

-- Use WHERE to filter before grouping (faster)
SELECT category, COUNT(*) AS product_count
FROM products
WHERE price > 10  -- Filter first
GROUP BY category
HAVING COUNT(*) > 5;  -- Then filter groups

-- Create indexes on GROUP BY columns
CREATE INDEX idx_products_category ON products(category);

-- Use EXPLAIN to analyze
EXPLAIN ANALYZE
SELECT category, COUNT(*)
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

Best Practices

1. Use WHERE for row filtering, HAVING for group filtering

   -- Good
   SELECT category, AVG(price)
   FROM products
   WHERE stock > 0  -- Row filter
   GROUP BY category
   HAVING AVG(price) > 50;  -- Group filter

2. Reference aggregates in HAVING

   -- Good: Use aggregate in HAVING
   SELECT category, COUNT(*)
   FROM products
   GROUP BY category
   HAVING COUNT(*) > 10;
   
   -- Wrong: Use non-aggregate in HAVING
   -- HAVING price > 100  -- Error!

3. Use meaningful aliases

   SELECT 
       category,
       COUNT(*) AS product_count,
       AVG(price) AS avg_price
   FROM products
   GROUP BY category
   HAVING product_count > 10 AND avg_price > 50;

4. Combine with ORDER BY for ranked results

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

Common Mistakes

-- ❌ Using column alias in HAVING (may not work in all cases)
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING cnt > 10;  -- May fail

-- ✅ Use aggregate function
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- ❌ Filtering individual rows with HAVING
SELECT * FROM products
HAVING price > 100;  -- Wrong! Use WHERE

-- ✅ Use WHERE for row filtering
SELECT * FROM products
WHERE price > 100;

Summary

HAVING clause key points:

• Filters groups after GROUP BY
• Used with aggregates: COUNT, SUM, AVG, MIN, MAX
• Difference from WHERE: WHERE filters rows, HAVING filters groups
• Execution order: WHERE → GROUP BY → HAVING → SELECT → ORDER BY
• Performance: Use WHERE to filter before grouping when possible
• Combine with: GROUP BY, ORDER BY, LIMIT