What is HAVING?

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Filter departments with average salary above 60000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

-- Using alias in HAVING
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 60000;

WHERE vs HAVING

-- WHERE filters rows before grouping
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000;

Multiple Conditions in HAVING

-- Multiple HAVING conditions
SELECT department, job_title, COUNT(*) AS cnt
FROM employees
GROUP BY department, job_title
HAVING COUNT(*) > 3 AND AVG(salary) > 50000;

-- Combining OR and AND in HAVING
SELECT department, COUNT(*) AS cnt, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 OR SUM(salary) > 500000;

HAVING with Aggregate Functions

-- Using multiple aggregate functions in HAVING
SELECT department,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING MAX(salary) - MIN(salary) > 30000;

-- HAVING with COUNT and SUM
SELECT department, COUNT(*) AS cnt, SUM(salary) AS total
FROM employees
GROUP BY department
HAVING COUNT(*) >= 10 AND SUM(salary) > 1000000;

Real-World Examples

-- Find departments with high turnover (many terminated employees)
SELECT department,
       COUNT(*) AS total_employees,
       SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) AS terminated
FROM employees
GROUP BY department
HAVING SUM(CASE WHEN status = 'terminated' THEN 1 ELSE 0 END) > 3;

-- Identify products with insufficient stock
SELECT category,
       AVG(stock_quantity) AS avg_stock,
       MIN(stock_quantity) AS min_stock
FROM products
GROUP BY category
HAVING MIN(stock_quantity) < 10;

HAVING with JOIN

-- HAVING with joined tables
SELECT d.department_name, COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.department_name
HAVING COUNT(*) > 10;

-- Complex HAVING with multiple joins
SELECT d.department_name,
       e.job_title,
       COUNT(*) AS cnt
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN salaries s ON e.id = s.employee_id
GROUP BY d.department_name, e.job_title
HAVING AVG(s.salary) > 80000 AND COUNT(*) > 5;

Common HAVING Patterns