Loading…
Loading…
SQL tutorial · Intermediate
⏱️ 12 mins read
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.
SELECT COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col)
FROM table;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;| 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 |
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;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;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 65666Trying 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.
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).
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?
Return a single value: the total number of employees.
⚡ Solve it in the SQL playground →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.
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.