Velamuvi AIAcademy

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
In this lesson you will learn
  • 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?

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
SELECT ROUND(AVG(age), 1) AS average_age
FROM students;

MIN and MAX

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.
Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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

Practice exercise

Count the students whose country is India.

Your solutionSQL
-- How many students are from India?
-- Return one number, in a column called india_students
Knowledge check

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, MIN and MAX summarise a column.
  • WHERE is 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.