Level 5: Summarising a table: aggregates

Lesson

So far, each row of the table gave one row of result. An aggregate function does the opposite: it sums up several rows into a single value. COUNT counts, SUM adds up, AVG computes the average, MIN and MAX give the smallest and the largest value.

Without anything else, the aggregate covers the whole table and the result fits on a single row. SELECT COUNT(*), AVG(rating) FROM movies answers two questions at once: how many movies, and what average rating.

COUNT has two forms. COUNT(*) counts all the rows. COUNT(column) only counts the rows where the column is filled: on the movies, COUNT(*) is 10 but COUNT(director_id) is 9, because Deep Current has no director. The other aggregates also ignore NULL: AVG averages only the known values.

MIN and MAX also work on text (alphabetical order) and on dates (the oldest, the most recent).

The WHERE applies before the aggregate: you first choose the rows, then you sum them up. SELECT AVG(grade) FROM grades WHERE subject = 'math' gives the average of the maths grades only. If no row passes the filter, COUNT returns 0, but SUM, AVG, MIN and MAX return NULL.

ROUND(value, n) rounds to n decimal places: ROUND(AVG(grade), 1). An average often has many decimals: round it for display.

An important limit: you cannot show an ordinary column next to an aggregate without grouping. SELECT title, MAX(rating) makes no sense in standard SQL: MAX gives a single value, but which title should be shown next to it? Level 14 shows the right method.

Syntax

SELECT COUNT(*) AS nb, AVG(column) AS average, MAX(column) AS maximum
FROM my_table
WHERE …;

Worked example

SELECT COUNT(*) AS nb_movies,
       AVG(rating) AS avg_rating,
       MAX(duration) AS longest
FROM movies;

The whole table is summarised in a single row: 10 movies, their average rating and the length of the longest one.

Example result

Key points

• An aggregate without GROUP BY returns a single row.
• COUNT(*) counts rows, COUNT(column) counts non-empty values.
• WHERE filters before the calculation.

Common pitfalls

• Mixing a plain column and an aggregate without grouping (SELECT title, MAX(rating)) is an error in standard SQL, even though SQLite accepts it.
• AVG on a column that contains NULLs averages only the known values.

SQLite specifics

SQLite accepts SELECT title, MAX(rating) FROM movies and returns the title of the best-rated movie: this is a “bare column”, a SQLite peculiarity. PostgreSQL, SQL Server and Oracle reject this query. The portable method (a subquery) comes in level 14.

The level’s 5 exercises

1. Guided · How many books are there in the books table? (Table: books)
2. Practice · How many seats were sold in total, across all flights? (Table: flights)
3. Practice · Show the date of the first loan (first_loan) and the date of the last loan (last_loan). (Table: loans)
4. Practice · How many contacts have an email address? (Table: contacts)
5. Challenge · For the maths grades, show on a single row: the number of grades (nb_grades), their average rounded to one decimal place (avg_math) and the gap between the best and the worst grade (spread). (Table: grades)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic