Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Aggregate functions collapse many rows into a single summary value — this is how you go from raw data to insights.

The five essentials

Function	Returns
COUNT()	number of rows
SUM()	total of a numeric column
AVG()	average of a numeric column
MIN()	smallest value
MAX()	largest value

SELECT COUNT(*) AS total_employees FROM employees;

SELECT SUM(price) AS inventory_value FROM products;

SELECT AVG(salary) AS average_salary FROM employees;

SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products;

COUNT(*) vs COUNT(column)

COUNT(*) counts rows, including ones with NULLs. COUNT(column) counts only rows where that specific column is not NULL:

-- Counts all employees
SELECT COUNT(*) FROM employees;

-- Counts only employees who HAVE a manager
SELECT COUNT(manager_id) FROM employees;

COUNT(DISTINCT ...)

SELECT COUNT(DISTINCT category) AS number_of_categories FROM products;

Combining aggregates with WHERE

WHERE filters rows before they're aggregated:

SELECT AVG(salary) AS avg_engineering_salary
FROM employees
WHERE department_id = 1;

Rounding results

SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.