Aggregate functions — COUNT, SUM, AVG, MIN, MAX

Aggregate functions collapse multiple rows into a single value.

The five core aggregates

SELECT
  COUNT(*)          AS total_employees,
  COUNT(manager_id) AS employees_with_manager,   -- skips NULLs
  SUM(salary)       AS total_payroll,
  AVG(salary)       AS average_salary,
  MIN(salary)       AS lowest_salary,
  MAX(salary)       AS highest_salary
FROM employees;

COUNT(*) vs COUNT(column)

NULLs in aggregates

SUM, AVG, MIN, MAX all ignore NULL values. If all rows are NULL, the result is NULL (not zero).

Using WHERE with aggregates

WHERE runs before aggregation, so you can filter the rows being aggregated:

-- Average salary only for the IT department
SELECT AVG(salary) AS it_avg
FROM employees
WHERE department_id = 60;

Tips

• Without GROUP BY, aggregate functions condense the entire table into one row.
• You cannot mix aggregate and non-aggregate columns in SELECT without GROUP BY (or a window function).