HAVING Clause

-- Find customers with more than 5 orders and total spend > $1000
SELECT
    customer_id,
    COUNT(*)            AS order_count,
    SUM(total)          AS total_spent,
    AVG(total)          AS avg_order_value
FROM orders
WHERE status <> 'CANCELLED'          -- ✓ filter rows BEFORE grouping (WHERE)
GROUP BY customer_id
HAVING order_count > 5               -- ✓ filter groups AFTER aggregating (HAVING)
   AND total_spent > 1000
ORDER BY total_spent DESC;

-- Products with average rating below 3 and at least 10 reviews
SELECT
    product_id,
    AVG(rating)   AS avg_rating,
    COUNT(*)      AS review_count
FROM reviews
GROUP BY product_id
HAVING avg_rating < 3.0 AND review_count >= 10;

-- HAVING without GROUP BY — rare but valid (entire table is one group)
SELECT COUNT(*) AS total FROM orders HAVING total > 1000;

-- ✗ Anti-pattern: HAVING instead of WHERE for non-aggregate filter
SELECT customer_id, COUNT(*) AS cnt
FROM orders
GROUP BY customer_id
HAVING customer_id > 100;  -- scans ALL groups then filters — slow

-- ✓ Correct: use WHERE to push row filter before GROUP BY
SELECT customer_id, COUNT(*) AS cnt
FROM orders
WHERE customer_id > 100    -- filters rows first — fewer rows to aggregate
GROUP BY customer_id;

-- Both clauses together — very common pattern
SELECT
    department_id,
    COUNT(*)          AS headcount,
    AVG(salary)       AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'      -- only recent hires (WHERE — row filter)
GROUP BY department_id
HAVING avg_salary > 60000            -- only high-paying depts (HAVING — group filter)
ORDER BY avg_salary DESC;

-- Aggregate functions overview
SELECT
    category,
    COUNT(*)                    AS total_products,
    COUNT(discount_pct)         AS products_with_discount,  -- NULLs excluded
    SUM(stock)                  AS total_stock,
    AVG(price)                  AS avg_price,
    MIN(price)                  AS cheapest,
    MAX(price)                  AS most_expensive,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
                                AS product_names  -- "Gadget, Widget, ..."
FROM products
GROUP BY category;

-- ROLLUP — adds subtotals and grand total
SELECT
    COALESCE(category, 'ALL')   AS category,
    SUM(total)                  AS revenue
FROM orders
GROUP BY category WITH ROLLUP;
-- Outputs: Electronics | 50000
--          Books       | 12000
--          ALL         | 62000  ← ROLLUP-generated grand total

-- DISTINCT inside aggregate
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;