What is the difference between WHERE and HAVING in SQL?

| id | name    | department | salary |
|----|---------|-----------|--------|
|  1 | Alice   | QA        | 95000  |
|  2 | Bob     | Dev       | 120000 |
|  3 | Charlie | QA        | 85000  |
|  4 | Diana   | Dev       | 135000 |
|  5 | Eve     | QA        | 110000 |
|  6 | Frank   | HR        | 65000  |

-- Only look at QA employees, then group
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
WHERE department = 'QA'    -- ← filter ROWS first
GROUP BY department;

-- Result:
-- QA | 3 | 96666.67

-- WHERE with multiple conditions
SELECT name, salary
FROM employees
WHERE salary > 90000
  AND department IN ('QA', 'Dev');
-- Alice, Bob, Diana, Eve

-- Group first, then filter groups where avg salary > 100000
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;   -- ← filter GROUPS

-- Result:
-- Dev | 2 | 127500

-- WRONG: Using aggregate in WHERE (throws error)
-- SELECT department, AVG(salary)
-- FROM employees
-- WHERE AVG(salary) > 100000   ← ❌ ERROR
-- GROUP BY department;

-- Active employees (WHERE), grouped by dept (GROUP BY),
-- only depts with 2+ members (HAVING)
SELECT department,
       COUNT(*)      AS headcount,
       AVG(salary)   AS avg_salary,
       MAX(salary)   AS max_salary
FROM employees
WHERE status = 'active'          -- 1. filter active rows
GROUP BY department              -- 2. group them
HAVING COUNT(*) >= 2             -- 3. only groups with 2+ people
ORDER BY avg_salary DESC;        -- 4. sort result

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- Find departments where total salary expense > 200000
SELECT department, SUM(salary) AS total_expense
FROM employees
GROUP BY department
HAVING SUM(salary) > 200000;

-- Result:
-- Dev | 255000
-- QA  | 290000