Aggregate functions: COUNT, SUM, AVG, MIN and MAX
An aggregate function turns many rows into one value — COUNT counts, SUM adds, AVG averages, MIN and MAX find the smallest and largest — and every one except COUNT(*) skips NULLs.
Why it matters
Nobody wants to read 10 million orders. They want "how many?", "how much in total?", "what is the average?". Aggregates answer those in one line — but the NULL rules quietly change the answers if you don't know them.
The idea
Without GROUP BY, an aggregate squashes the whole table (after WHERE) into one row.COUNT(*) counts rows.COUNT(column) counts rows where that column is not NULL.COUNT(DISTINCT column) counts the different non-NULL values.
SUM and AVG add and average the non-NULL values.
MIN and MAX work on numbers, text (alphabetical) and dates.
Because AVG skips NULLs, the average of order amounts is the total divided by 9 orders, not 10.
A cashier at the end of the day. They don't list every sale; they count the receipts (COUNT), add up the takings (SUM), and note the biggest and smallest sale (MAX, MIN). A receipt with the amount torn off still counts as a receipt, but it adds nothing to the takings.
A cashier would notice the torn receipt and ask about it. SQL doesn't: it silently leaves NULLs out of SUM and AVG, so you have to decide yourself whether a missing value should count as zero.
Syntax
SELECT COUNT(*), COUNT(column), COUNT(DISTINCT column),
SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name;How it works
- FROM and WHERE decide which rows are used — here all 10 orders.
COUNT(*)counts the rows: 10.COUNT(amount)counts rows whose amount is not NULL: 9, because order 106 has none.SUM(amount)adds the 9 amounts: 793.AVG(amount)is 793 ÷ 9 = 88.11, not 793 ÷ 10.- If the question means "average per order, counting the missing one as zero", divide SUM by
COUNT(*)instead: 79.3.
Key points
COUNT(*)counts rows;COUNT(col)counts non-NULL values;COUNT(DISTINCT col)counts different non-NULL values.- SUM, AVG, MIN and MAX ignore NULLs.
- SUM of no rows (or only NULLs) is NULL, not 0. Wrap it:
COALESCE(SUM(x), 0). - Without GROUP BY, an aggregate query returns exactly one row — even from an empty table.
- MIN and MAX work on text and dates too.
Common mistakes
- Using
COUNT(column)when you mean "how many rows", and getting a smaller number because of NULLs. - Mixing a plain column with an aggregate without GROUP BY:
SELECT name, MAX(salary) FROM employeesis an error in most databases (SQLite allows it but the name is unreliable). - Putting an aggregate in WHERE:
WHERE SUM(amount) > 100is an error. That is HAVING's job.
Not true: An aggregate such as SUM can be used in WHERE to filter on a total.
In fact: WHERE works on single rows, before any total exists. Filtering on an aggregate is the job of HAVING.
In the interview
Aggregate functions collapse a set of rows into one value: COUNT, SUM, AVG, MIN and MAX. The detail interviewers probe is NULL handling. COUNT star counts rows, and COUNT of a column skips NULLs. SUM and AVG ignore NULLs, so an average is over the known values only. I also remember that SUM over no rows is NULL, so I COALESCE it to zero when the result feeds a report.
Part of Unit 2: Summarising data: aggregates, GROUP BY and HAVING in the free SQL course.