Aggregate functions take many rows and return a single summary value. The five you must know are COUNT, SUM, AVG, MIN and MAX. They are the basis of reporting and, combined with GROUP BY, of nearly every analytical query.
The five aggregates
COUNT counts rows, SUM totals a numeric column, AVG averages it, and MIN/MAX find the extremes. A crucial detail: aggregates (except COUNT(*)) ignore NULLs.
Summarising the employees table
SELECT COUNT(*) AS headcount, -- all rows
AVG(salary) AS avg_salary,
MAX(salary) AS top_salary,
MIN(salary) AS low_salary,
SUM(salary) AS payroll
FROM employees;COUNT(*) vs COUNT(column)
SELECT COUNT(*) AS total_rows, -- counts every row
COUNT(manager_id) AS has_manager -- skips NULLs
FROM employees;⚡ The edge
- COUNT(*) counts every row including NULLs; COUNT(column) skips NULLs in that column. This difference is a favourite interview catch.
- AVG, SUM, MIN and MAX all ignore NULLs — so AVG(salary) divides by the count of non-null salaries, not by the total row count.
Worked example
How many employees have a manager, and what is the company's average salary?
- Employees with a manager have a non-null manager_id; COUNT(manager_id) skips the NULLs.
- The average salary is AVG(salary), which also ignores any NULL salaries.
- Both can be returned in one SELECT over the employees table.
Answer: SELECT COUNT(manager_id) AS has_mgr, AVG(salary) AS avg_sal FROM employees;
Worked example
What is the total project budget?
- You need the sum of the budget column in the projects table.
- Use SUM(budget).
- SUM ignores NULL budgets if any exist.
Answer: SELECT SUM(budget) FROM projects; -- 500000 + 300000 + 150000 = 950000
⚠ Watch out
- COUNT(*) includes NULL rows; COUNT(col) does not.
- AVG ignores NULLs, which can surprise you — it is not SUM/total-rows.
- You cannot put a bare non-aggregated column beside an aggregate without GROUP BY (next chapter).