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.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;