Aggregating: COUNT, SUM, AVG, MIN/MAX and GROUP BY

Compute totals, averages and per-group statistics using aggregate functions and GROUP BY.

8 min · 3 questions

An aggregate function takes several rows as input and returns a single value: a total, an average, a minimum... The simplest is COUNT(*), which counts the rows. It combines naturally with WHERE: the database filters first, then counts.

SELECT COUNT(*) FROM books;

SELECT COUNT(*) FROM books WHERE genre = 'sf';

The other essential aggregates apply to a column: SUM(price) adds up, AVG(price) computes the average, MIN(year) and MAX(year) find the smallest and largest values. The AS keyword gives an alias, that is, a readable name for the computed column.

SELECT SUM(price) AS total FROM books;

SELECT AVG(price) AS avg_sf
FROM books
WHERE genre = 'sf';

SELECT MIN(year), MAX(year) FROM books;

And to get a statistic per category? That is the role of GROUP BY: the database groups the rows that share the same value (here the genre), then applies the aggregate to each group. The result contains one row per group.

SELECT genre, COUNT(*) AS nb
FROM books
GROUP BY genre;

SELECT genre, AVG(price) AS avg_price
FROM books
GROUP BY genre;

Knowledge check

Make sure you remember the key points of this lesson.

1. What does SELECT COUNT(*) FROM books; return?
   • The number of columns in the table
   • The number of rows in the table
   • The list of all the books
2. Which function computes the average price of the books?
   • SUM(price)
   • AVG(price)
   • MEAN(price)
   • MAX(price)
3. What does SELECT genre, COUNT(*) FROM books GROUP BY genre; produce?
   • One row per book, with its genre
   • A single row with the total number of books
   • One row per genre, with the number of books in that genre