Aggregate Functions
Intermediate⏱️ 12 mins read
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(manager_id) AS with_manager,
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, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
ORDER BY avg_sal DESC;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).
Practice
Find the department with the highest average salary. Show that department's min, max, average salary, and headcount.