SQL from Zero › Module 4: DQL: asking questions

Counting and totals

Turn many rows into one answer: how many, how much, the cheapest and the dearest.

Lesson 23 of 35 · about 14 minutes

Video: From rows to answers

The lesson

So far each query has returned rows. Often you want a single answer instead: "How many books do we sell?" or "What's our average price?" Aggregate functions squash many rows into one value.

FunctionGives you
COUNT(*)how many rows
SUM(stock)the total of a column
AVG(price)the average
MIN(price) and MAX(price)the smallest and largest
SELECT COUNT(*) AS books, AVG(price) AS average_price
FROM books;

Combine with WHERE to summarise only some rows. The filter happens first, then the counting:

SELECT COUNT(*) FROM books WHERE stock = 0;

Do sums inside. SUM(price * stock) is the value of everything on the shelves.

Two handy details:

  • COUNT(*) counts rows. COUNT(city) counts only rows where city isn't NULL.
  • COUNT(DISTINCT genre) counts different values, so 6 rather than 20.

Long decimals look messy. ROUND(AVG(price), 2) rounds to two decimal places.

Key ideas
  • COUNT, SUM, AVG, MIN and MAX turn many rows into one value.
  • WHERE filters the rows first, then the function summarises what's left.
  • COUNT(*) counts rows; COUNT(column) skips NULLs; COUNT(DISTINCT column) counts different values.

Practise

Loading practice…

Quick check

1. How many rows does SELECT AVG(price) FROM books; return?

2. Two of eight customers have no city. What does COUNT(city) return?

3. Which comes first when this runs?

SELECT SUM(stock) FROM books WHERE genre = 'Children';