Having

Chapter 19: HAVING Clause

Filtering Grouped Data

---

19.1 Understanding HAVING

The HAVING clause filters groups after GROUP BY aggregation, unlike WHERE which filters rows before grouping.

                    WHERE vs HAVING Flow
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                    SQL Query Execution Order                        │
    │                                                                      │
    │   1. FROM        → Identify source tables                           │
    │   2. WHERE       → Filter rows BEFORE grouping                      │
    │   3. GROUP BY    → Group rows                                       │
    │   4. HAVING      → Filter groups AFTER grouping                    │
    │   5. SELECT      → Project columns                                  │
    │   6. ORDER BY    → Sort results                                     │
    │                                                                      │
    │   ┌─────────────────┐      ┌─────────────────┐                    │
    │   │    WHERE        │      │    HAVING       │                    │
    │   │                 │      │                 │                    │
    │   │ Filters:        │      │ Filters:        │                    │
    │   │ • Individual    │      │ • Aggregated    │                    │
    │   │   rows          │      │   values        │                    │
    │   │ • Before        │      │ • After         │                    │
    │   │   aggregation  │      │   aggregation   │                    │
    │   │ • Cannot use    │      │ • Can use       │                    │
    │   │   aggregates    │      │   aggregates     │                    │
    │   └─────────────────┘      └─────────────────┘                    │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

Basic HAVING Examples

-- Sample data
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    product VARCHAR(100),
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    order_date DATE
);

INSERT INTO orders (customer_id, product, quantity, unit_price, order_date) VALUES
    (1, 'Laptop', 1, 999.99, '2024-01-15'),
    (1, 'Mouse', 2, 29.99, '2024-01-15'),
    (2, 'Keyboard', 1, 79.99, '2024-01-16'),
    (2, 'Monitor', 2, 299.99, '2024-01-16'),
    (3, 'Laptop', 1, 999.99, '2024-01-17'),
    (3, 'Headphones', 1, 149.99, '2024-01-17'),
    (1, 'Webcam', 1, 89.99, '2024-01-18');

-- HAVING: Filter groups with total order value > 500
SELECT
    customer_id,
    SUM(quantity * unit_price) as total_spent,
    COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > 500
ORDER BY total_spent DESC;

---

19.2 HAVING vs WHERE

Understanding when to use each clause:

-- WHERE: Filter individual rows BEFORE grouping
SELECT customer_id, product, quantity
FROM orders
WHERE quantity >= 2;  -- Individual rows with qty >= 2

-- HAVING: Filter groups AFTER aggregation
SELECT customer_id, SUM(quantity) as total_qty
FROM orders
GROUP BY customer_id
HAVING SUM(quantity) >= 3;  -- Customers who ordered 3+ total items

-- Using both WHERE and HAVING
SELECT
    customer_id,
    product,
    SUM(quantity) as total_quantity
FROM orders
WHERE product IN ('Laptop', 'Mouse', 'Keyboard')  -- Filter rows first
GROUP BY customer_id, product
HAVING SUM(quantity) > 1  -- Then filter groups
ORDER BY customer_id, product;

---

19.3 HAVING with Various Aggregate Functions

-- HAVING with COUNT
-- Find customers with more than 2 orders
SELECT
    customer_id,
    COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;

-- HAVING with AVG
-- Find products with average price > 100
SELECT
    product,
    AVG(unit_price) as avg_price,
    COUNT(*) as order_count
FROM orders
GROUP BY product
HAVING AVG(unit_price) > 100
ORDER BY avg_price DESC;

-- HAVING with MIN/MAX
-- Find categories where price range exceeds 500
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    name VARCHAR(100),
    price DECIMAL(10,2)
);

INSERT INTO products (category, name, price) VALUES
    ('Electronics', 'Laptop', 999.99),
    ('Electronics', 'Mouse', 29.99),
    ('Electronics', 'Keyboard', 79.99),
    ('Clothing', 'Shirt', 29.99),
    ('Clothing', 'Jacket', 199.99);

SELECT
    category,
    MIN(price) as min_price,
    MAX(price) as max_price,
    MAX(price) - MIN(price) as price_range
FROM products
GROUP BY category
HAVING MAX(price) - MIN(price) > 100
ORDER BY price_range DESC;

---

19.4 Multiple Conditions in HAVING

-- Multiple conditions with AND
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(quantity * unit_price) as total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(quantity * unit_price) >= 100
ORDER BY total_spent DESC;

-- Multiple conditions with OR
SELECT
    product,
    COUNT(*) as times_ordered,
    SUM(quantity) as total_quantity
FROM orders
GROUP BY product
HAVING COUNT(*) >= 3 OR SUM(quantity) >= 5
ORDER BY total_quantity DESC;

-- Complex HAVING with subquery
SELECT
    customer_id,
    COUNT(*) as order_count,
    AVG(quantity * unit_price) as avg_order_value
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > (
    SELECT COUNT(*) / COUNT(DISTINCT customer_id)
    FROM orders
)
ORDER BY order_count DESC;

---

19.5 HAVING with NULL Handling

-- Create table with NULLs
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    region VARCHAR(50),
    salesperson VARCHAR(50),
    amount DECIMAL(10,2)
);

INSERT INTO sales (region, salesperson, amount) VALUES
    ('North', 'Alice', 1000),
    ('North', 'Bob', 1500),
    ('South', NULL, 800),
    ('South', 'Charlie', 1200),
    ('East', 'Diana', NULL),
    ('West', 'Eve', 2000);

-- HAVING ignores NULL groups by default (no grouping on NULL)
SELECT
    region,
    COUNT(*) as sales_count,
    SUM(amount) as total_sales
FROM sales
GROUP BY region
HAVING SUM(amount) > 1500;

-- Using COALESCE in GROUP BY to include NULLs
SELECT
    COALESCE(salesperson, 'Unknown') as salesperson,
    SUM(amount) as total_sales
FROM sales
GROUP BY COALESCE(salesperson, 'Unknown')
HAVING SUM(amount) > 500;

---

19.6 Practical Examples

E-commerce Analytics

-- Find VIP customers (high value, multiple orders)
SELECT
    customer_id,
    COUNT(*) as total_orders,
    SUM(quantity * unit_price) as lifetime_value,
    AVG(quantity * unit_price) as avg_order_value,
    MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(quantity * unit_price) >= 500
ORDER BY lifetime_value DESC;

-- Find underperforming products
SELECT
    product,
    COUNT(*) as times_ordered,
    SUM(quantity) as total_sold,
    AVG(unit_price) as avg_price
FROM orders
GROUP BY product
HAVING COUNT(*) < 2
   OR SUM(quantity) < 2
ORDER BY total_sold;

-- Time-based analysis with HAVING
SELECT
    DATE_TRUNC('day', order_date) as day,
    COUNT(*) as daily_orders,
    SUM(quantity * unit_price) as daily_revenue
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01'
GROUP BY DATE_TRUNC('day', order_date)
HAVING SUM(quantity * unit_price) > 1000
ORDER BY day;

---

19.7 HAVING with Subqueries

-- Subquery in HAVING clause
SELECT
    customer_id,
    SUM(quantity * unit_price) as total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(quantity * unit_price) > (
    SELECT AVG(total_spent)
    FROM (
        SELECT customer_id, SUM(quantity * unit_price) as total_spent
        FROM orders
        GROUP BY customer_id
    ) avg_customer
);

-- Correlated subquery in HAVING
SELECT
    product,
    SUM(quantity) as total_sold
FROM orders
GROUP BY product
HAVING SUM(quantity) > (
    SELECT AVG(quantity_sum)
    FROM (
        SELECT SUM(quantity) as quantity_sum
        FROM orders
        GROUP BY product
    ) product_totals
);

---

19.8 Common Patterns and Anti-Patterns

    HAVING Best Practices
    ========================================================================

    ✓ DO:
    ───────────────
    • Use HAVING for aggregate filtering
    • Combine with WHERE for better performance
    • Use meaningful aggregate conditions
    • Consider using CASE in HAVING for complex logic

    ✗ DON'T:
    ───────────────
    • Use HAVING when WHERE would work
    • Reference non-aggregated columns incorrectly
    • Forget that HAVING runs after GROUP BY

    Performance Tip:
    ───────────────
    • WHERE reduces rows before grouping (faster)
    • HAVING filters after aggregation (slower)
    • Always filter early when possible!

---

Summary

Clause	When to Use	Can Use Aggregates
WHERE	Filter rows before grouping	No
HAVING	Filter groups after grouping	Yes
Both	Combine for precise filtering	Yes

---

Next: Chapter 20: Window Functions