SQL Aggregates: GROUP BY, HAVING, and Aggregate Functions

Aggregate functions

Aggregate functions compute a value from a set of rows.

SELECT
    COUNT(*)           AS total_rows,
    COUNT(salary)      AS non_null_salaries,
    COUNT(DISTINCT department) AS dept_count,
    SUM(salary)        AS total_payroll,
    AVG(salary)        AS avg_salary,
    MIN(salary)        AS min_salary,
    MAX(salary)        AS max_salary
FROM employees;

GROUP BY

GROUP BY groups rows with the same value in specified columns so aggregates apply per group.

-- Average salary per department
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

-- Multiple grouping columns
SELECT department, job_title, COUNT(*) AS headcount
FROM employees
GROUP BY department, job_title;

HAVING — filtering groups

HAVING filters groups after aggregation. WHERE filters rows before aggregation.

-- Departments with more than 5 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC;

-- WHERE vs HAVING:
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date >= '2020-01-01'  -- filter rows BEFORE grouping
GROUP BY department
HAVING AVG(salary) > 70000;      -- filter groups AFTER grouping

The execution order of a SELECT

SQL clauses execute in a specific order — this explains many "why can't I use this here?" questions.

-- Logical execution order:
-- 1. FROM / JOIN
-- 2. WHERE
-- 3. GROUP BY
-- 4. HAVING
-- 5. SELECT
-- 6. ORDER BY
-- 7. LIMIT

-- This is why you can't use a SELECT alias in WHERE:
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;  -- ERROR: alias not yet defined

-- Fix: use the expression directly
WHERE salary * 12 > 100000;

Exam tip

WHERE vs HAVING is the #1 aggregate interview question. WHERE runs before GROUP BY (filters rows). HAVING runs after GROUP BY (filters groups). You cannot use aggregate functions in WHERE.