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
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.
| Function | Gives 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';