SQL COUNT, SUM, AVG, MIN and MAX

Counting and adding up

So far every query has returned one result row per table row. Aggregate functions do the opposite: they squash lots of rows into a single answer.

SELECT COUNT(*) FROM orders;

That is "how many orders are there?", answered as one row with one number. The big five:

COUNT(*) counts rows
SUM(qty) adds up a column
AVG(total) gives the average (the mean)
MIN(total) and MAX(total) give the smallest and largest

You can use several at once, and alias them like any column:

SELECT COUNT(*) AS orders, SUM(total) AS takings, MAX(total) AS biggest
FROM orders;

They team up with WHERE, which filters first. So this counts only Zara's orders:

SELECT COUNT(*) FROM orders WHERE customer = 'Zara';

Now the NULL twist from last lesson. COUNT(*) counts rows, full stop. COUNT(column) counts only the rows where that column is not NULL. In this table some orders have no voucher, so COUNT(*) and COUNT(voucher) give different answers, and both are right. They just answer different questions. SUM, AVG, MIN and MAX also skip NULLs, so an average ignores the missing values instead of treating them as zero.

One rule to remember: in a query like SELECT customer, COUNT(*) FROM orders, which customer should appear next to a count of all the orders? There is no sensible answer. Many databases refuse that query; SQLite runs it and grabs some customer from some row, which looks like an answer and isn't one. Mixing plain columns with aggregates needs GROUP BY, which is next lesson.

Tables in this lesson

orders 9 rows

id	customer	snack	qty	total	voucher
1	Zara	Crisps	2	2.5	NULL
2	Leo	Energy drink	1	2.75	STUDENT10
3	Zara	Gummy worms	3	6.0	NULL
4	Maya	Chocolate bar	4	6.0	STUDENT10
5	Leo	Crisps	1	1.25	NULL
6	Sam	Mystery sandwich	1	3.25	NULL
7	Zara	Apple	6	3.0	HEALTHY
8	Maya	Instant noodles	2	3.5	NULL
9	Leo	Energy drink	2	5.5	NULL

Show the SQL that built them

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, snack TEXT, qty INTEGER, total REAL, voucher TEXT);
INSERT INTO orders VALUES
 (1, 'Zara', 'Crisps', 2, 2.5, NULL),
 (2, 'Leo', 'Energy drink', 1, 2.75, 'STUDENT10'),
 (3, 'Zara', 'Gummy worms', 3, 6.0, NULL),
 (4, 'Maya', 'Chocolate bar', 4, 6.0, 'STUDENT10'),
 (5, 'Leo', 'Crisps', 1, 1.25, NULL),
 (6, 'Sam', 'Mystery sandwich', 1, 3.25, NULL),
 (7, 'Zara', 'Apple', 6, 3.0, 'HEALTHY'),
 (8, 'Maya', 'Instant noodles', 2, 3.5, NULL),
 (9, 'Leo', 'Energy drink', 2, 5.5, NULL);

Try it yourself

Edit it. Break it. Run it again.

SELECT COUNT(*) AS orders,
       COUNT(voucher) AS with_voucher,
       SUM(total) AS takings,
       MAX(total) AS biggest
FROM orders;

Your turn

Type it yourself. That is the whole trick.

1How many crisps?

Return a single number: the total qty of Crisps sold across all orders.

SELECT COUNT(*)
FROM orders
WHERE snack = 'Crisps';

2Smallest, biggest, average

Return one row with three columns: the smallest total, the largest total and the average total of all orders, in that order.

SELECT MIN(total)
FROM orders;