GROUP BY & HAVING

-- Correct — category is in both SELECT and GROUP BY
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Error — name is in SELECT but not in GROUP BY and not aggregated
SELECT category, name, COUNT(*) AS product_count
FROM products
GROUP BY category;   -- fails: column "name" must appear in GROUP BY or aggregate

-- Fixed — either aggregate name or add it to GROUP BY
SELECT category, COUNT(*) AS product_count, MAX(name) AS sample_product
FROM products
GROUP BY category;

-- Order count and revenue by status AND month
SELECT
  status,
  EXTRACT(MONTH FROM order_date) AS order_month,
  COUNT(*)                        AS order_count,
  ROUND(SUM(total_amount), 2)     AS revenue
FROM orders
GROUP BY status, EXTRACT(MONTH FROM order_date)
ORDER BY order_month, status;

-- Products grouped by category and active status
SELECT category, is_active,
       COUNT(*) AS product_count,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category, is_active
ORDER BY category, is_active DESC;

-- Employees grouped by department and job title
SELECT department, job_title,
       COUNT(*) AS headcount,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;

-- Revenue by year and month using DATE_TRUNC
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*)                         AS orders,
  ROUND(SUM(total_amount), 2)      AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Group customers into tiers using CASE
SELECT
  CASE
    WHEN joined_date >= CURRENT_DATE - INTERVAL '1 year' THEN 'New'
    WHEN joined_date >= CURRENT_DATE - INTERVAL '3 years' THEN 'Established'
    ELSE 'Loyal'
  END AS customer_tier,
  COUNT(*) AS customer_count
FROM customers
GROUP BY customer_tier
ORDER BY customer_count DESC;

-- Products grouped by price band
SELECT
  CASE
    WHEN price < 30  THEN 'Budget (under $30)'
    WHEN price < 100 THEN 'Mid-range ($30–$99)'
    ELSE 'Premium ($100+)'
  END AS price_band,
  COUNT(*) AS product_count,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY price_band
ORDER BY avg_price;

-- Categories with more than 5 products AND average price above $40
SELECT category,
       COUNT(*) AS product_count,
       ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5 AND AVG(price) > 40
ORDER BY avg_price DESC;

-- Customers who placed between 3 and 8 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) BETWEEN 3 AND 8
ORDER BY order_count DESC;

-- States with 2 to 5 customers
SELECT state, COUNT(*) AS customer_count
FROM customers
GROUP BY state
HAVING COUNT(*) BETWEEN 2 AND 5
ORDER BY customer_count DESC, state;

-- Active products only, grouped by category, categories with avg > $40
SELECT category,
       COUNT(*) AS active_products,
       ROUND(AVG(price), 2) AS avg_price,
       SUM(stock_qty) AS total_stock
FROM products
WHERE is_active = TRUE                  -- row filter first
GROUP BY category
HAVING AVG(price) > 40                  -- group filter after
ORDER BY avg_price DESC;

-- Delivered orders in 2024, customers with more than $300 total
SELECT customer_id,
       COUNT(*) AS order_count,
       ROUND(SUM(total_amount), 2) AS total_spent
FROM orders
WHERE status = 'delivered'              -- only delivered orders
  AND order_date >= '2024-01-01'        -- only 2024
GROUP BY customer_id
HAVING SUM(total_amount) > 300          -- only big spenders
ORDER BY total_spent DESC;

-- Alias works in ORDER BY but not in HAVING
SELECT
  customer_id,
  COUNT(*)                    AS order_count,
  ROUND(SUM(total_amount), 2) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total_amount) > 200      -- must repeat the expression in HAVING
ORDER BY total_spent DESC           -- can use the alias in ORDER BY
LIMIT 10;