Know if you're actually ready. Take the SQL quiz → get your AI readiness report.Take the free test →

Study guide · SQL

SQL Aggregates: GROUP BY, HAVING, and Aggregate Functions

Aggregation is tested in every SQL interview. Here's what you need to know — GROUP BY, HAVING, and the WHERE vs HAVING trap.

Examifyr·2026·5 min read

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;

NoteCOUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values. This difference is frequently tested.

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;

NoteEvery column in SELECT that is not an aggregate must appear in GROUP BY. Violating this causes an error in strict SQL databases.

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

NoteYou cannot use aggregate functions in WHERE. Use HAVING for aggregate conditions.

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;

NoteThe execution order explains why aliases defined in SELECT can't be used in WHERE or HAVING — they don't exist yet at that stage.

Exam tipWHERE 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.

Want a person to help?

Struggling with SQL? Work 1:1 with a vetted SQL tutor on Wyzant. (affiliate link)

Find a SQL tutor →

← All SQL guides