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:

SELECT MIN(hire_date) AS first_hired,
       MAX(hire_date) AS last_hired,
       MIN(last_name) AS first_alphabetically,
       MAX(last_name) AS last_alphabetically
FROM Employees;

The dates come out right because they are stored as YYYY-MM-DD text, and in that format alphabetical order is date order. SQLite, which runs the examples here, has no separate date type, so a date stored as 20/05/2016 would be compared as plain text and give the wrong answer.

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) * 1.0 / 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.

The * 1.0 is not decoration. Both SUM(manager_id) and COUNT(*) are whole numbers, and in SQLite, PostgreSQL and SQL Server dividing one integer by another gives an integer: without it, 37 / 12 comes back as 3, with the fraction simply dropped. Multiplying by 1.0, or casting one side to a decimal type, keeps the fractional part. MySQL is the odd one out, because its / keeps the fractional part even between two integers.

When no rows match: NULL, not zero

An aggregate over nothing is a special case, and it catches reports out. The sample orders are only ever Shipped, Pending or Cancelled, so asking about refunded orders leaves no rows to aggregate:

SELECT COUNT(*)                      AS orders,
       SUM(order_total)              AS revenue,
       COALESCE(SUM(order_total), 0) AS revenue_or_zero
FROM Orders
WHERE status = 'Refunded';

COUNT says 0, because counting nothing gives zero. SUM says NULL: with no values there is no total, and the same goes for AVG, MIN and MAX. That is standard SQL, and PostgreSQL, MySQL, SQL Server and SQLite all behave this way. It matters when the number is added to something else, because anything plus NULL is NULL, or when it lands in a report column people read as money. When zero is the answer you mean, say so with COALESCE, as the third column does.

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. PostgreSQL, SQL Server and MySQL in its default mode raise an error. SQLite, which runs this site’s playground, is one of the few that accept the query and fill the column from an arbitrary row of each group, which is worse because nothing looks wrong. The playground adds a warning under the result when that happens.

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, ROUND(AVG(salary), 0) AS avg_salary
FROM Employees
WHERE AVG(salary) >= 100000
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 department average to compare against. Filtering on an aggregate is what HAVING is for:

SELECT dept_id, ROUND(AVG(salary), 0) AS avg_salary
FROM Employees
GROUP BY dept_id
HAVING AVG(salary) >= 100000
ORDER BY avg_salary 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 usually cheaper. The GROUP BY vs HAVING guide takes this further, with both filters in one query and what each database allows in HAVING.

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 earlier on this page 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