SQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
· 6 min read
SQL aggregate functions take many rows and return one value. The five you will use every day are COUNT (how many), SUM (the total), AVG (the average), MIN (the smallest) and MAX (the largest). Used alone they summarise the whole table; with GROUP BY they summarise each group.
SELECT COUNT(*) AS orders,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM orders
WHERE status = 'completed';One query, one row, five answers. Below: the output on realistic data, what each function does with NULLs, per-group and conditional versions, dialect differences, and the averaging mistake that inflates a KPI.
A worked example
| order_id | region | status | amount | discount_amount |
|---|---|---|---|---|
| 1 | North | completed | 120 | NULL |
| 2 | North | completed | 80 | 10 |
| 3 | North | cancelled | 200 | NULL |
| 4 | South | completed | 300 | 30 |
| 5 | South | completed | 50 | NULL |
| 6 | South | completed | 100 | 5 |
| 7 | West | completed | 400 | NULL |
| 8 | West | refunded | 60 | NULL |
Run the query from the top on it:
SELECT COUNT(*) AS orders,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount,
MIN(amount) AS smallest,
MAX(amount) AS largest
FROM orders
WHERE status = 'completed';| orders | total_amount | avg_amount | smallest | largest |
|---|---|---|---|---|
| 6 | 1050 | 175 | 50 | 400 |
The WHERE runs first, so only the six completed orders are summarised. The cancelled 200 and the refunded 60 never reach the functions.
SQL COUNT: three versions
COUNT has three forms, and they answer different questions:
SELECT COUNT(*) AS all_rows,
COUNT(discount_amount) AS rows_with_discount,
COUNT(DISTINCT region) AS regions
FROM orders;| all_rows | rows_with_discount | regions |
|---|---|---|
| 8 | 3 | 3 |
COUNT(*)counts rows, whatever is in them.COUNT(column)counts rows where that column is notNULL.COUNT(DISTINCT column)counts different values.
Mixing these up is behind many wrong numbers. When to use the distinct version, and how to find duplicates with it, is covered in SQL COUNT DISTINCT.
SQL SUM and SQL AVG
SUM adds up a numeric column and AVG divides that sum by the number of values. Both skip NULLs, and for AVG that changes the answer:
SELECT AVG(discount_amount) AS avg_discount_given,
AVG(COALESCE(discount_amount, 0)) AS avg_discount_all_orders
FROM orders
WHERE status = 'completed';| avg_discount_given | avg_discount_all_orders |
|---|---|
| 15 | 7.5 |
The first is the average over the three orders that had a discount (45 / 3). The second treats a missing discount as 0 and averages over all six (45 / 6). Neither is wrong, but they answer different questions, so decide which one you mean. More on this in SQL COALESCE and NULL.
Two more details:
SUMof zero rows isNULL, not 0.SELECT SUM(amount) FROM orders WHERE region = 'East'returnsNULL(andCOUNT(*)returns 0). Wrap it inCOALESCE(SUM(amount), 0)if a report needs a zero.AVGon whole numbers is a dialect trap. DuckDB, Postgres and MySQL return a decimal. SQL Server returns a whole number when the column is an integer, so the average of 1 and 2 comes back as 1. WriteAVG(amount * 1.0)or cast the column to a decimal in SQL Server.
Use ROUND(AVG(amount), 2) to show two decimals in the output. Round at the end, never before you sum.
MIN and MAX
MIN and MAX work on numbers, dates and text. On dates they give the first and last date, which is a quick way to check what period a table covers: SELECT MIN(order_date), MAX(order_date) FROM orders. On text they sort alphabetically: on this table MIN(region) is North and MAX(region) is West.
Aggregates with GROUP BY
Add GROUP BY and each function runs once per group:
SELECT region,
COUNT(*) AS orders,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM orders
WHERE status = 'completed'
GROUP BY region
ORDER BY region;| region | orders | total_amount | avg_amount |
|---|---|---|---|
| North | 2 | 200 | 100 |
| South | 3 | 450 | 150 |
| West | 1 | 400 | 400 |
Every column in SELECT must either be inside an aggregate or listed in GROUP BY. SELECT region, SUM(amount) FROM orders without GROUP BY fails with column "region" must appear in the GROUP BY clause. Grouping by more than one column is covered in GROUP BY multiple columns. To filter groups by their totals, use HAVING (see HAVING vs WHERE).
Conditional aggregates
Sometimes you need several filtered totals side by side. Put the condition inside the aggregate:
SELECT region,
COUNT(*) AS all_orders,
COUNT(*) FILTER (WHERE status = 'completed') AS done,
SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS done_amount
FROM orders
GROUP BY region
ORDER BY region;| region | all_orders | done | done_amount |
|---|---|---|---|
| North | 3 | 2 | 200 |
| South | 3 | 3 | 450 |
| West | 2 | 1 | 400 |
FILTER (WHERE ...) works in DuckDB, Postgres and SQLite. SUM(CASE WHEN ...) works everywhere, including MySQL and SQL Server.
Common mistakes
Averaging averages (the AI mistake to watch for)
Ask an AI assistant for "the average completed order amount" after it has already produced the per-region table, and it may reuse that table:
SELECT ROUND(AVG(avg_amount), 2) AS avg_order_amount
FROM (
SELECT region, AVG(amount) AS avg_amount
FROM orders
WHERE status = 'completed'
GROUP BY region
) AS by_region;| avg_order_amount |
|---|
| 216.67 |
The real average order is 175 (1050 / 6), the number in the first example. The query above gives each region an equal vote, so West's single 400 order counts as much as South's three orders together. The bigger the difference in group sizes, the further off the answer is, and it usually looks perfectly reasonable.
How to spot it: an AVG of a column that is itself an average (avg_..., mean_...) is almost always wrong. Recalculate from the raw rows, or compute SUM(total) / SUM(count) from the grouped table. Check it against the overall AVG on the base table.
COUNT(column) when you meant COUNT(*)
COUNT(discount_amount) returns 3, not 8. If the column has NULLs, it is not a row count.
Summing a column that repeats after a join
After joining orders to order lines, an order-level column like a shipping fee repeats on every line. SUM adds it once per line. Aggregate at the right level first.
FAQ
What are the 5 aggregate functions in SQL?
COUNT, SUM, AVG, MIN and MAX. Most databases have more, like STRING_AGG or MEDIAN, but those five cover most analysis work.
Do aggregate functions ignore NULL values?
Yes, all of them except COUNT(*). SUM, AVG, MIN, MAX and COUNT(column) skip NULLs.
Can I use an aggregate function in WHERE?
No. WHERE filters rows before they are aggregated. Put conditions on aggregates in HAVING.
How do I count rows that meet a condition?
Add a WHERE if it applies to the whole query. For several conditions in one result, use COUNT(*) FILTER (WHERE ...) or SUM(CASE WHEN ... THEN 1 ELSE 0 END).
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “How many customers?” →