Lesson 6 of 17 · Summarising Data
COUNT, SUM and AVG
Summarise data with SQL aggregate functions. Count rows, add up values and find averages, minimums and maximums, with runnable examples.
- 8 min read
- Beginner
- Reviewed
- What an aggregate function is
- How to count rows and add up a column
- How to find an average, a minimum and a maximum
- The difference between COUNT(*) and COUNT(column)
From many rows to one answer
So far every query has returned rows from a table. But many questions have a single number as their answer. How many students are there? What is the average score? What is the longest course?
An aggregate function takes many rows and turns them into one value.
COUNT: how many?
SELECT COUNT(*) AS total_students FROM students;
COUNT(*) counts rows. The result is a single row with a single number.
Add a WHERE clause to count only some rows. The filter is applied first, then the remaining rows are counted.
SELECT COUNT(*) AS finished FROM enrollments WHERE progress = 100;
SUM and AVG
SUM adds up a column. AVG gives the average, also called the mean.
SELECT SUM(hours) AS total_hours,
AVG(hours) AS average_hours
FROM courses;Averages often have many decimal places. ROUND(value, digits) tidies them up.
SELECT ROUND(AVG(age), 1) AS average_age FROM students;
MIN and MAX
SELECT MIN(age) AS youngest,
MAX(age) AS oldest
FROM students;MIN and MAX also work on text and dates. MIN(joined) is the earliest date, and MAX(joined) the latest.
SELECT MIN(joined) AS first_signup,
MAX(joined) AS latest_signup
FROM students;Several summaries at once
One query can calculate as many aggregates as you like.
SELECT COUNT(*) AS enrollments,
ROUND(AVG(score), 1) AS average_score,
MIN(score) AS lowest_score,
MAX(score) AS highest_score
FROM enrollments;COUNT(*) or COUNT(column)?
They look similar but answer different questions.
COUNT(*)counts rows.COUNT(column)counts rows where that column has a value. Missing values (NULL) are skipped.
SELECT COUNT(*) AS all_enrollments,
COUNT(score) AS with_a_score
FROM enrollments;There are 18 enrollments, but only 12 have a score. The other six students have not finished their course yet.
All the other aggregates skip NULL too. That is why the average score above was calculated from 12 values, not 18.
Counting different values
Put DISTINCT inside COUNT to count how many different values a column has.
SELECT COUNT(DISTINCT country) AS countries FROM students;
One important rule
An aggregate squeezes many rows into one. An ordinary column still has one value per row. You cannot mix them in the same SELECT. Asking for name next to MAX(age) is not a reliable way to find the oldest student. Most database systems reject it. A few accept it, each with its own rules about which name comes back, so do not depend on it.
To find out who is oldest, sort and limit, as you learned in the last lesson.
SELECT name, age FROM students ORDER BY age DESC LIMIT 1;
In the next lesson you will learn the proper way to combine ordinary columns with aggregates: GROUP BY.
Common mistakes
Count the students whose country is India.
-- How many students are from India? -- Return one number, in a column called india_students
A table has 10 rows. In 3 of them, the score column is NULL. What does COUNT(score) return?
Summary
- An aggregate function turns many rows into one value.
COUNT(*)counts rows.SUM,AVG,MINandMAXsummarise a column.WHEREis applied before the aggregate.- Aggregates skip
NULL.COUNT(column)counts only rows with a value. COUNT(DISTINCT column)counts different values.
Next: one summary per group.
Finished reading? Tick it off to track your progress.