Aggregate Functions

Intermediate

⏱️ 12 mins read

Runs over the employees table from Topic 1 — aggregates meet Edsger's NULL department_id head-on.

What You'll Learn

Aggregate functions compute a single result from multiple rows. Core functions: COUNT (rows), SUM (total), AVG (mean), MIN (smallest), MAX (largest). All aggregates except COUNT(*) ignore NULL values. They're always used with GROUP BY or applied to the entire table.

Syntax

SELECT COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
FROM table;

Example

SELECT
  COUNT(*)            AS total_employees,
  COUNT(department_id) AS with_department,  -- Edsger's NULL is skipped
  SUM(salary)          AS total_payroll,
  ROUND(AVG(salary), 2) AS avg_salary,
  MIN(salary)          AS lowest,
  MAX(salary)          AS highest
FROM employees;

-- Aggregate per group
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal DESC;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary | hire_date
-- 1  | Ada     | 1             | 145000 | 2021-03-01
-- 2  | Grace   | 1             | 115000 | 2022-06-13
-- 3  | Alan    | 2             | 92000  | 2020-11-02
-- 4  | Edsger  | NULL          | 78000  | 2023-08-19
-- 5  | Barbara | 3             | 64000  | 2024-01-08

COUNT(*) and COUNT(col) disagree — by exactly one row

Run the example above and total_employees says 5, but with_department says 4. The missing employee is Edsger: COUNT(department_id) skips his NULL. Every aggregate except COUNT(*) silently drops NULL rows — the classic 'why doesn't the report add up' bug.

SELECT
  COUNT(*)              AS all_rows,   -- 5
  COUNT(department_id)  AS with_dept,  -- 4 — Edsger dropped
  SUM(salary)           AS payroll,    -- 494000 — all 5 (salary is never NULL)
  ROUND(AVG(salary), 2) AS avg_salary  -- 98800 — divided by 5, not 4
FROM employees;
AVG(salary) is literally SUM(salary) / COUNT(salary) — the denominator silently ignores NULLs. Know which rows your average is really over.

SUM over zero rows is NULL, not 0

Aggregate an empty set and SUM, AVG, MIN, and MAX return NULL — only COUNT returns 0. A revenue report that shows a blank (instead of 0.00) for a quiet month is almost always this bug.

-- No department 99 exists, so this filters out every row:
SELECT
  SUM(salary)                AS payroll,     -- NULL, not 0
  COUNT(*)                   AS n,           -- 0
  COALESCE(SUM(salary), 0)   AS payroll_safe -- 0 — decide, don't inherit
FROM employees
WHERE department_id = 99;
Empty input → SUM says 'unknown', COUNT says 'zero'. Wrap financial aggregates in COALESCE when the report must show 0.

Conditional aggregates: one scan, many filtered answers

Because aggregates skip NULLs, wrapping a column in CASE lets one query compute several filtered statistics at once — no GROUP BY, no second scan. Topic 11 makes CASE a first-class tool; here it is already earning its keep.

-- Engineering's average salary in a single pass over the table:
SELECT
  ROUND(AVG(CASE WHEN department_id = 1 THEN salary END), 2) AS eng_avg
FROM employees;
-- CASE yields salary for dept 1 and NULL for everyone else —
-- AVG ignores those NULLs, so the answer is 130000, not 65666
AVG(CASE WHEN cond THEN col END) is the seed of pivot reporting — Topic 11 formalizes it, Topic 18 replaces it with window functions.

Common Mistakes

Trying to SELECT a non-aggregated column alongside an aggregate without GROUP BY. Also: AVG(salary) divides only by rows where salary IS NOT NULL — if many rows have NULL salary, the average can be misleadingly high.

Interview Tips

COUNT(*) vs COUNT(col): COUNT(*) counts all rows, COUNT(col) skips NULLs in that column. Interviewers love this distinction. Also know that SUM of zero rows returns NULL, not 0 — use COALESCE(SUM(col), 0).

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. SELECT COUNT(*), COUNT(department_id) FROM employees — what does ShopCo return?

2. AVG(salary) over ShopCo is exactly…?

3. SUM(salary) over a WHERE that matches zero rows returns…?

4. AVG(CASE WHEN department_id = 1 THEN salary END) over all employees returns…?

5. Which aggregate behaves differently from all the others around NULL?

6. What does SUM(salary) return for a department with no employees?

Practice

Return a single value: the total number of employees.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Does AVG(salary) divide by COUNT(*) or COUNT(salary)?

COUNT(salary). AVG, SUM, MIN, and MAX all ignore NULL rows, so AVG(salary) is exactly SUM(salary)/COUNT(salary). If NULLs should count as zero in the mean, compute SUM(salary)/COUNT(*) explicitly.

What do aggregates return on an empty table?

COUNT(*) returns 0; SUM, AVG, MIN, and MAX return NULL. That NULL is why reports sometimes show blank instead of zero — COALESCE(SUM(x), 0) is the standard fix.

Back to Full Roadmap