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

  1. FROM and WHERE decide which rows are used — here all 10 orders.
  2. COUNT(*) counts the rows: 10.
  3. COUNT(amount) counts rows whose amount is not NULL: 9, because order 106 has none.
  4. SUM(amount) adds the 9 amounts: 793. AVG(amount) is 793 ÷ 9 = 88.11, not 793 ÷ 10.
  5. If the question means "average per order, counting the missing one as zero", divide SUM by COUNT(*) instead: 79.3.

Key points

Common mistakes

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.