HAVING Clause

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

-- Categories with at least 4 products
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) >= 4;

-- Categories with average price > $150
SELECT
  category,
  COUNT(*) AS products,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 150;

-- WRONG: Can't use aggregate in WHERE
SELECT category, COUNT(*) AS product_count
FROM products
WHERE COUNT(*) > 3  -- ❌ ERROR!
GROUP BY category;

-- CORRECT: Use HAVING for aggregate conditions
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 3;  -- ✓ WORKS!

-- WHERE: Filter rows where price > 100 (BEFORE grouping)
SELECT category, COUNT(*) AS product_count
FROM products
WHERE price > 100
GROUP BY category;

-- HAVING: Filter groups with count > 2 (AFTER grouping)
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

-- Categories with >2 expensive products (price > 100)
SELECT category, COUNT(*) AS expensive_products
FROM products
WHERE price > 100        -- Filter: only expensive products
GROUP BY category        -- Group by category
HAVING COUNT(*) > 2;     -- Filter: categories with >2 products

-- Departments with 2+ experienced employees (3+ years) and avg salary > 70k
SELECT
  department,
  COUNT(*) AS experienced_employees,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE years_experience >= 3  -- Only experienced employees
GROUP BY department
HAVING COUNT(*) >= 2         -- At least 2 employees
   AND AVG(salary) > 70000;  -- Average salary > 70k

-- Categories with 3+ products AND average price between 100-400
SELECT
  category,
  COUNT(*) AS product_count,
  ROUND(AVG(price), 2) AS avg_price,
  SUM(stock) AS total_stock
FROM products
GROUP BY category
HAVING COUNT(*) >= 3
   AND AVG(price) BETWEEN 100 AND 400;

-- Categories with low count OR low total stock
SELECT
  category,
  COUNT(*) AS products,
  SUM(stock) AS total_stock
FROM products
GROUP BY category
HAVING COUNT(*) < 3
    OR SUM(stock) < 200;

-- Departments with 2+ employees, sorted by average salary
SELECT
  department,
  COUNT(*) AS employees,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

-- Using alias in HAVING (works in MySQL, not all databases)
SELECT
  category,
  COUNT(*) AS product_count,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING product_count > 3
   AND avg_price > 150;

-- Products ordered more than 2 times
SELECT
  product_id,
  COUNT(*) AS times_ordered,
  SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > 2
ORDER BY times_ordered DESC;

-- Customers with 2+ orders and total spending > $200
SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent,
  ROUND(AVG(total_amount), 2) AS avg_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(total_amount) > 200
ORDER BY total_spent DESC;

-- Large departments (5+ employees) with high avg salary (>75k)
SELECT
  department,
  COUNT(*) AS headcount,
  ROUND(AVG(salary), 2) AS avg_salary,
  MIN(salary) AS min_salary,
  MAX(salary) AS max_salary
FROM employees
WHERE salary >= 60000  -- Only consider employees earning 60k+
GROUP BY department
HAVING COUNT(*) >= 2
   AND AVG(salary) > 75000
ORDER BY avg_salary DESC;