SQL Aggregate Functions — COUNT, SUM, AVG, MIN, MAX

Aggregate Functions in SQL

Aggregate functions compute a single value from a set of rows. They are the building blocks of reporting queries and are almost always used with GROUP BY.

The Five Core Aggregates

Function	Returns
COUNT(*)	Number of rows
COUNT(col)	Number of non-NULL values in column
COUNT(DISTINCT col)	Number of unique non-NULL values
SUM(col)	Total of all values
AVG(col)	Arithmetic mean
MIN(col)	Smallest value
MAX(col)	Largest value

All-at-Once Example

SELECT
  COUNT(*) AS total_rows,
  COUNT(manager_id) AS has_manager,
  COUNT(DISTINCT department_id) AS num_departments,
  SUM(salary) AS total_payroll,
  AVG(salary) AS avg_salary,
  MIN(salary) AS lowest_salary,
  MAX(salary) AS highest_salary
FROM employees;

With GROUP BY

SELECT
  department_id,
  COUNT(*) AS headcount,
  ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;

NULL Handling

• COUNT(*) counts all rows, including those with NULLs.
• COUNT(col), SUM, AVG, MIN, MAX all ignore NULL values.
• If all values are NULL, SUM and AVG return NULL (not zero).

Combining Aggregates with CASE

SELECT
  COUNT(CASE WHEN salary >= 80000 THEN 1 END) AS senior_count,
  COUNT(CASE WHEN salary < 80000 THEN 1 END) AS junior_count
FROM employees;

This technique is called a conditional aggregate and is very powerful for creating pivot-style reports in a single query.