Aggregate functions take a whole column of values — across the entire table, or within each group if you've used GROUP BY — and collapse them down into a single summary number. COUNT, SUM, AVG, MIN, and MAX are the five you'll reach for constantly.

They work the same way whether you're summarizing the whole table or one group at a time, which is why they pair so naturally with GROUP BY.

COUNT, SUM, and AVG

SELECT
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS average_salary
FROM employees;

Result:

total_employees | total_payroll | average_salary
----------------|---------------|---------------
8 | 584000 | 73000

MIN and MAX

MIN and MAX aren't limited to numbers — they work on dates and text too, returning the earliest/latest or alphabetically first/last value:

SELECT
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary,
    MIN(hire_date) AS earliest_hire
FROM employees;

Result:

lowest_salary | highest_salary | earliest_hire
--------------|----------------|---------------
54000 | 95000 | 2018-06-25

COUNT(*) vs COUNT(column)

This difference catches people off guard. COUNT(*) counts every row, full stop. COUNT(column_name) only counts rows where that specific column is not NULL — any row with a NULL in that column is silently skipped.

-- Suppose 2 employees have no department assigned yet (NULL)
SELECT
    COUNT(*) AS all_rows,
    COUNT(department) AS rows_with_department
FROM employees;

Result:

all_rows | rows_with_department
---------|---------------------
8 | 6

If you actually want to count distinct values rather than all of them, combine it with DISTINCT: COUNT(DISTINCT department) tells you how many unique departments exist, regardless of how many employees are in each.

Aggregates Per Group

Every one of these functions becomes far more useful once you pair it with GROUP BY to get a result per category instead of one number for the whole table — see the GROUP BY tutorial for the full breakdown of how that works, and HAVING for filtering on these aggregate results afterward.