Aggregate functions: COUNT, SUM, AVG, MIN and MAX

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  A["10 order rows<br/>120, 80, 45, 200, 150,<br/>NULL, 60, 75, 30, 33"] --> C["COUNT(*) = 10"]
  A --> N["COUNT(amount) = 9<br/>NULL skipped"]
  A --> S["SUM(amount) = 793"]
  A --> V["AVG(amount) = 793 / 9"]

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.

The shape of it

SELECT COUNT(*), COUNT(column), COUNT(DISTINCT column),
       SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name;

Words in lower case are the parts you fill in.

How it works, step by step

1. FROM and WHERE decide which rows are in play — 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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT COUNT(*) AS employees,
       SUM(salary) AS total_pay,
       AVG(salary) AS average_pay,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM employees;

SELECT COUNT(*) AS all_rows,
       COUNT(dept_id) AS with_a_department,
       COUNT(DISTINCT dept_id) AS different_departments
FROM employees;

SELECT COUNT(*) AS orders,
       COUNT(amount) AS with_amount,
       SUM(amount) AS total,
       ROUND(AVG(amount), 2) AS avg_of_known,
       ROUND(SUM(amount) * 1.0 / COUNT(*), 2) AS avg_over_all
FROM orders;

Must-know 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 employees is an error in most databases (SQLite allows it but the name is unreliable).
• Putting an aggregate in WHERE: WHERE SUM(amount) > 100 is an error. That is HAVING's job.

In other databases

• AVG of whole numbers: PostgreSQL and SQLite return a decimal; SQL Server returns a whole number (AVG of 1 and 2 is 1). Use AVG(x * 1.0) there.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  W3Schools — SQL COUNT() Function

  short page with a try-it editor, free

• Read

  Mode SQL Tutorial — SQL Aggregate Functions, COUNT, SUM, MIN/MAX, AVG

  Mode, free

• Practice

  SQLBolt — Lesson 10: Queries with aggregates (Pt. 1)

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why AVG(amount) on orders is 88.11 and not 79.3.

   Show answer

   AVG skips NULLs. There are 10 orders, but order 106 has no amount, so only 9 amounts are averaged. The 9 known amounts add up to £793, and 793 ÷ 9 = 88.11.

   79.3 is 793 ÷ 10: it treats the missing amount as £0. That is a different question ("average per order, counting a missing amount as nothing"). If that is what you want, say so: SUM(amount) * 1.0 / COUNT(*), or AVG(COALESCE(amount, 0)).

   sqlCopy

   SELECT COUNT(*) AS orders,
          COUNT(amount) AS with_amount,
          SUM(amount) AS total,
          ROUND(AVG(amount), 2) AS avg_amount
   FROM orders;

   Try it

   1 row

   orders	with_amount	total	avg_amount
   10	9	793	88.11

2. Writefrom a blank page, then run it

   Write one query that returns the number of products, the cheapest price, the dearest price and the average price.

   Show answer

   Four aggregates in one SELECT, with no GROUP BY, give one summary row for the whole table. ROUND keeps the average readable: 125 ÷ 7 = 17.857…

   sqlCopy

   SELECT COUNT(*) AS products,
          MIN(price) AS cheapest,
          MAX(price) AS dearest,
          ROUND(AVG(price), 2) AS average_price
   FROM products;

   Try it

   1 row

   products	cheapest	dearest	average_price
   7	2	40	17.86

3. Predictsay it before you run it

   What do COUNT(*), COUNT(city) and COUNT(DISTINCT city) return on the customers table?

   Show answer

   6, 5 and 3.

   • COUNT(*) counts rows: 6 sign-ups.
   • COUNT(city) counts values that are not NULL: Zoe has no city, so 5.
   • COUNT(DISTINCT city) counts different cities: London, Leeds and Bristol, so 3. The NULL is not counted, and the two Londons and two Leeds count once each.

   sqlCopy

   SELECT COUNT(*), COUNT(city), COUNT(DISTINCT city)
   FROM customers;

   Try it

   1 row

   COUNT(*)	COUNT(city)	COUNT(DISTINCT city)
   6	5	3

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   What does SELECT SUM(amount), COUNT(*) FROM orders WHERE status = 'refunded'; return? Why isn't it 0 and 0?

   Show answer

   One row: NULL and 0.

   No order is 'refunded', so the aggregates see no rows. COUNT of nothing is 0. But SUM of nothing is NULL, not 0, because there is nothing to add up. (Without GROUP BY, an aggregate query always returns exactly one row, even when no rows match.)

   If a report should show 0, write COALESCE(SUM(amount), 0).

   sqlCopy

   SELECT SUM(amount), COUNT(*)
   FROM orders
   WHERE status = 'refunded';

   Try it

   1 row

   SUM(amount)	COUNT(*)
   NULL	0

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, COUNT of a column skips NULLs, and 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.

Follow-up questions

Tick each check when you can do it without notes