HAVING vs WHERE

-- Goal: find departments where active employees' average salary exceeds 70 000

-- WRONG: using HAVING to filter non-aggregated conditions (status check)
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000
   AND status = 'active';   -- ERROR or logical mistake: status is a row-level filter
-- In PostgreSQL this actually raises an error because status is not in GROUP BY.

-- CORRECT: WHERE eliminates inactive rows first, HAVING filters groups
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'              -- row-level filter: runs BEFORE GROUP BY
GROUP BY department_id
HAVING AVG(salary) > 70000;         -- group-level filter: runs AFTER GROUP BY

-- The WHERE version is also faster: fewer rows enter the GROUP BY.

-- Find departments where the average salary of employees hired after 2019
-- exceeds the company-wide average salary.

SELECT
    d.name         AS department,
    COUNT(e.id)    AS headcount,
    AVG(e.salary)  AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE e.hire_date > '2019-12-31'          -- WHERE: filter rows before grouping
GROUP BY d.name
HAVING AVG(e.salary) > (                  -- HAVING: filter groups after grouping
    SELECT AVG(salary) FROM employees WHERE hire_date > '2019-12-31'
)
ORDER BY avg_salary DESC;

-- Execution order reminder (logical, not necessarily physical):
-- 1. FROM employees JOIN departments   → combined row set
-- 2. WHERE hire_date > '2019-12-31'    → rows eliminated early
-- 3. GROUP BY d.name                   → groups formed
-- 4. HAVING AVG(salary) > (subquery)   → groups eliminated
-- 5. SELECT d.name, COUNT, AVG         → projection
-- 6. ORDER BY avg_salary DESC          → sort output

-- Mistake 1: referencing a SELECT alias in HAVING (invalid in most DBs)
-- because HAVING executes before SELECT in the logical order.
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING avg_sal > 70000;     -- ERROR in PostgreSQL/MySQL strict mode
-- Fix: repeat the expression:
HAVING AVG(salary) > 70000;

-- Mistake 2: using WHERE to filter an aggregate (syntax error)
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 70000    -- SYNTAX ERROR: aggregate not allowed in WHERE
GROUP BY department_id;

-- Acceptable: HAVING without GROUP BY (filters the single grand-total group)
SELECT COUNT(*), AVG(salary)
FROM employees
HAVING COUNT(*) > 100;       -- returns the row only if total headcount > 100