Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Turn a whole column of values into one useful answer

Big Idea: Most queries give you back many rows. An aggregate function is different: it reads a whole group of rows and hands back one value - a count, a total, an average, the smallest, or the largest.

By the end you will be able to:

count rows with COUNT(*) and count values with COUNT(column),
add a column up with SUM and find the average with AVG,
find the smallest and largest with MIN and MAX,
explain how NULL values are treated by each function,
aggregate only the rows you want by adding a WHERE clause.

1. What is an aggregate function?

Think of a blender. You drop in a whole pile of fruit, press the button, and out comes one glass of juice. An aggregate function works the same way: you point it at a whole column of values, and it blends them down into a single answer.

A normal SELECT price FROM toys would show you six separate prices - six rows. But SELECT SUM(price) FROM toys reads all six prices and returns just one number: their total. The word aggregate simply means "to gather many things into one".

The five aggregate functions you will meet most often are:

COUNT - how many?
SUM - added together, what is the total?
AVG - what is the average?
MIN - what is the smallest?
MAX - what is the largest?

2. Our example: a toy-shop table

Every query below runs against one small table called toys. Here it is, created once and filled with six toys. Notice the discount column: two toys have no discount yet, so their value is NULL (meaning "unknown / not set").

CREATE TABLE toys (
  id       INT,
  name     VARCHAR(30),
  price    DECIMAL(5,2),
  stock    INT,
  discount DECIMAL(4,2)
);

INSERT INTO toys VALUES
  (1, 'Teddy Bear', 12.50,  8, NULL),
  (2, 'Race Car',    9.00, 15, 2.00),
  (3, 'Kite',        6.50,  0, NULL),
  (4, 'Puzzle',      4.00, 20, 1.00),
  (5, 'Doll',       15.00,  5, 3.00),
  (6, 'Yo-yo',       3.00, 30, NULL);

The whole table looks like this:

id	name	price	stock	discount
1	Teddy Bear	12.50	8	NULL
2	Race Car	9.00	15	2.00
3	Kite	6.50	0	NULL
4	Puzzle	4.00	20	1.00
5	Doll	15.00	5	3.00
6	Yo-yo	3.00	30	NULL

Six rows, and the discount column has three real numbers and three NULLs. Keep that in mind - it matters for COUNT and AVG.

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX