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

An aggregate function takes many rows and returns one value. There are five you will use constantly — COUNT, SUM, AVG, MIN and MAX — and almost every reporting query in existence is some combination of them.

They are easy to start using and easy to get subtly wrong, because two of their behaviours are invisible until they bite: what they do with NULL, and when they are allowed to appear. This page covers both, with every example runnable against the sample database.

All five at once

The whole family, applied to the orders table:

SELECT COUNT(*)          AS orders,
       SUM(order_total)  AS revenue,
       AVG(order_total)  AS average_order,
       MIN(order_total)  AS smallest,
       MAX(order_total)  AS largest
FROM Orders;

One row out, however many rows went in. That is the defining property of an aggregate: it collapses a set of values into a single value.

MIN and MAX on text give you alphabetical first and last, and on dates the earliest and latest — useful far more often than people expect.

COUNT(*) and COUNT(column) are not the same thing

This is the single most asked question about aggregates, and the answer is one word: NULL.

SELECT COUNT(*)          AS all_employees,
       COUNT(manager_id) AS have_a_manager
FROM Employees;

COUNT(*) counts rows. COUNT(manager_id) counts rows where manager_id actually has a value — the NULLs are skipped. The gap between the two numbers is exactly the number of employees with no manager, which you can confirm directly:

SELECT COUNT(*) AS no_manager
FROM Employees
WHERE manager_id IS NULL;

The AVG trap, made visible

Two ways of averaging the same column, side by side:

SELECT AVG(manager_id)                 AS avg_of_present_values,
       SUM(manager_id) / COUNT(*)      AS avg_over_every_row,
       COUNT(manager_id)               AS values_present,
       COUNT(*)                        AS rows_total
FROM Employees;

The two averages differ because they divide by different numbers. Neither is wrong — they answer different questions. What is wrong is not knowing which one you asked for. Averaging a manager id is meaningless in itself, of course; the point is that the same arithmetic applies to a column of prices or scores where the difference is money.

Aggregates with GROUP BY: one value per group

An aggregate on its own gives one row for the whole table. Add GROUP BY and you get one row per group:

SELECT d.dept_name,
       COUNT(e.emp_id)        AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary,
       MAX(e.salary)          AS top_salary
FROM Departments d
JOIN Employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY headcount DESC;

The rule that catches everyone: every column in the SELECT list that is not inside an aggregate must appear in the GROUP BY. If a group contains five different first names, the database has no way to choose one to show you, so it refuses. Most databases raise an error; a few pick a value arbitrarily, which is worse.

DISTINCT inside an aggregate

COUNT(DISTINCT column) counts how many different values there are, not how many rows:

SELECT COUNT(*)                  AS employees,
       COUNT(DISTINCT dept_id)   AS departments_with_people
FROM Employees;

Twelve employees spread across a smaller number of departments. This is how you answer “how many customers ordered this month” when the orders table has one row per order rather than per customer — COUNT(DISTINCT customer_id) rather than COUNT(*). Getting that wrong overstates the number, sometimes by a lot.

SUM(DISTINCT ...) and AVG(DISTINCT ...) exist too, but they are rarely what anyone means. If you find yourself reaching for them, the data probably needs de-duplicating first — see finding and deleting duplicate rows.

Why WHERE cannot filter an aggregate

This query looks reasonable and every database rejects it:

SELECT dept_id, COUNT(*) AS headcount
FROM Employees
WHERE COUNT(*) > 2
GROUP BY dept_id;

The reason is the order in which the clauses run. WHERE is evaluated before the rows have been grouped, so at that moment there is no count to compare against. Filtering on an aggregate is what HAVING is for:

SELECT dept_id, COUNT(*) AS headcount
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 2
ORDER BY headcount DESC;

Learn that sequence once and a whole category of errors stops being mysterious. Use WHERE to choose which rows go into the groups and HAVING to choose which groups survive — and put the row-level condition in WHERE even when both would work, because filtering earlier is always cheaper.

Conditional aggregation: counting several things at once

Put a CASE expression inside an aggregate and you can count or total different subsets in a single pass:

SELECT COUNT(CASE WHEN status = 'Shipped'   THEN 1 END) AS shipped,
       COUNT(CASE WHEN status = 'Pending'   THEN 1 END) AS pending,
       COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled,
       SUM(CASE WHEN status = 'Shipped' THEN order_total ELSE 0 END) AS shipped_revenue
FROM Orders;

This works because a CASE with no ELSE produces NULL for the rows that do not match, and COUNT skips NULLs — the same behaviour that was a trap two sections ago is the mechanism here. GROUP BY would give you the same numbers stacked vertically; this puts them side by side, which is the shape a dashboard wants.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• How many customers do we have?Easy
• Average salary, roundedEasy
• Cheapest and dearestEasy
• Headcount per departmentMedium
• Only the multi-person departmentsMedium
• One-row status reportMedium

See all 42 exercises →

Frequently asked questions

Related reading

• GROUP BY vs HAVING: which filter runs when
• CASE WHEN: conditional logic in queries
• Find and delete duplicate rows
• Window functions: aggregates that keep every row
• SQL for data analysts: the queries you actually write