Summarise with COUNT, SUM and AVG
Turn many rows into one with aggregate functions, and know what NULL does to them. About 9 minutes.
Many rows in, one row out
"How many books are there?" "What's the average mark?" These questions don't want a list. They want one number about the whole table.
An aggregate function reads all the rows and gives back one value: COUNT for how many, SUM for the total, AVG for the average, MIN and MAX for the smallest and largest. WHERE runs first, so you can aggregate just the rows that pass.
SELECT COUNT(*) AS papers,
SUM(score) AS total,
MAX(score) AS best
FROM marks
WHERE subject = 'Maths';What NULL and empty tables do
COUNT(*) counts rows, whatever they hold. COUNT(score) counts only the rows where score is not NULL, and SUM, AVG, MIN and MAX skip NULLs too. AVG divides by the values it saw, not the number of rows.
On an empty set, COUNT gives 0 but SUM, AVG, MIN and MAX give NULL, because there is nothing to add up.
| student | score |
|---|---|
| Asha | 80 |
| Ravi | NULL |
| Meera | 60 |
| Kabir | 100 |
COUNT(*) counts every row: 4.
A table has 5 rows, and cgpa is NULL in two of them. What does COUNT(cgpa) return?
- A3
- B5
- C2
Show the answer
3. COUNT(column) counts only the rows where the column is not NULL.
Class summary
The marks table has one row per paper, and a score that is NULL until it is marked. Show, in one row, the number of papers as papers, the average of the marked scores as average, and the highest score as best.
marks(id, student, score)
Up to 2,000 papers