Skip to content
aviral gupta
Get in touch

// B3.4 · ~30 min · Beginner

Aggregates and GROUP BY

After this lesson you can answer “how many” and “how much” in one query: for a whole table, or one row per genre, member or city.

Lesson 4 of 6 in B3 Joins and aggregates

You will be able to

  • Summarise a table with count, sum, avg, min and max, and round an average
  • Group rows with GROUP BY on one or two columns, also after a join
  • Predict aggregates over NULLs and over no rows, and sort groups by an aggregate

  1. Warm-up · Activity 1 of 7

    Warm-up: the books table has six rows. What does this query return?

    SELECT count(*) FROM books;

  2. Predict · Activity 2 of 7

    Predict before you read on. The six books have the genres sci-fi (three books), classic, fiction and fantasy. How many rows does this return?

    SELECT genre, count(*)
FROM books
GROUP BY genre;

  3. Practice · Activity 3 of 7

    Five loans were made by the members 1, 1, 2, 3 and 2. Fill in the key word so that the query counts the different members who borrowed, not the loans.

    SELECT count(____ member_id) FROM loans;
    SELECT count( member_id) FROM loans;

  4. Practice · Activity 4 of 7

    The books have 2, 1, 1, 1, 3 and 1 copies, in four genres. Match each expression to its value.

  5. Practice · Activity 5 of 7

    Put the rows in the order this query returns them: SELECT genre, sum(copies) AS copies FROM books GROUP BY genre ORDER BY copies DESC, genre;

    1. 1.fiction (1 copy)
    2. 2.classic (1 copy)
    3. 3.sci-fi (4 copies)
    4. 4.fantasy (3 copies)

  6. Brain teaser · Activity 6 of 7

    Brain teaser. The newest book is from 1987, so no book matches the WHERE. What does this return?

    SELECT count(*), sum(copies)
FROM books
WHERE year > 2000;

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example, write one query: for each city, the number of members and the date the first of them joined. Show a missing city as (unknown), put the biggest city first, and break ties by the join date. Before you run it, predict how many rows it returns.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Summing up the library

setup.sql loads the library of module B1: books, members and loans. main.sql summarises the catalogue in one row, counts the loans three ways, then groups the books by genre and, after a join, the loans by genre. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. The whole catalogue in one row.
SELECT count(*) AS books,
       sum(copies) AS copies,
       round(avg(copies), 2) AS avg_copies,
       min(year) AS oldest,
       max(year) AS newest
FROM books;

-- 2. Three ways to count the loans.
SELECT count(*) AS loans,
       count(returned_on) AS returned,
       count(DISTINCT member_id) AS borrowers
FROM loans;

-- 3. One row per genre, most copies first.
SELECT genre, count(*) AS books, sum(copies) AS copies
FROM books
GROUP BY genre
ORDER BY copies DESC, genre;

-- 4. Loans per genre: join first, then group.
SELECT b.genre, count(*) AS loans, count(DISTINCT l.book_id) AS titles
FROM loans l
JOIN books b ON b.id = l.book_id
GROUP BY b.genre
ORDER BY loans DESC;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 books | copies | avg_copies | oldest | newest
-------+--------+------------+--------+--------
     6 |      9 |       1.50 |   1815 |   1987
(1 row)

 loans | returned | borrowers
-------+----------+-----------
     5 |        2 |         3
(1 row)

  genre  | books | copies
---------+-------+--------
 sci-fi  |     3 |      4
 fantasy |     1 |      3
 classic |     1 |      1
 fiction |     1 |      1
(4 rows)

  genre  | loans | titles
---------+-------+--------
 sci-fi  |     4 |      3
 classic |     1 |      1
(2 rows)
  • The first query has aggregates and no GROUP BY, so all six books form one group and the result is one row.
  • count(returned_on) skips the three open loans, whose returned_on is NULL; count(DISTINCT member_id) counts members 1, 2 and 3 once each.
  • GROUP BY genre gives one row per genre; classic and fiction tie on copies, so the second sort key, genre, orders them.
  • In the last query the join runs first: sci-fi has four loans of three different books, because Dune was lent twice.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

A catalogue per genre and a member list per city

Write two queries. First: for each genre, the genre, the number of books (books), the total copies (copies) and the year of the oldest book (oldest), sorted by genre. Second: for each city of the members, the city, the number of members (members) and the latest join date (last_joined), sorted by city; the members without a city form a group of their own.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    GROUP BY genre gives one row per genre; count(*), sum(copies) and min(year) are then computed per genre.

  2. Hint 2

    Name the columns with AS: count(*) AS books, sum(copies) AS copies, min(year) AS oldest.

  3. Hint 3

    The second query is the same shape: GROUP BY city, count(*) AS members, max(joined) AS last_joined, ORDER BY city. The NULL city sorts last.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT genre, count(*) AS books, sum(copies) AS copies, min(year) AS oldest
FROM books
GROUP BY genre
ORDER BY genre;

SELECT city, count(*) AS members, max(joined) AS last_joined
FROM members
GROUP BY city
ORDER BY city;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
SELECT genre, title, year, copies FROM books ORDER BY genre;

test.sql

-- test: One row per genre with books, copies and the oldest year, then one row per city, the unknown city last
-- output:
--   genre  | books | copies | oldest
-- ---------+-------+--------+--------
--  classic |     1 |      1 |   1815
--  fantasy |     1 |      3 |   1937
--  fiction |     1 |      1 |   1987
--  sci-fi  |     3 |      4 |   1965
-- (4 rows)
--
--   city  | members | last_joined
-- --------+---------+-------------
--  Berlin |       2 | 2026-02-10
--  Munich |       1 | 2025-03-02
--         |       1 | 2026-05-20
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Loans per member, with names

The starter counts the loans per member_id. Make it a report a librarian can read: join members to show the member's name, and add the number of loans still open (open) and the date of the member's last loan (last_loan). Sort by name. An open loan has no returned_on.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    JOIN members m ON m.id = l.member_id, then group by the member: GROUP BY m.id, m.name.

  2. Hint 2

    m.name is in the select list, so it must be in GROUP BY too. m.id keeps two members with the same name apart.

  3. Hint 3

    count(*) counts all loans, count(l.returned_on) only the returned ones; the difference is the open loans. max(l.loaned_on) is the last loan.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT m.name,
       count(*) AS loans,
       count(*) - count(l.returned_on) AS open,
       max(l.loaned_on) AS last_loan
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY m.name;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Loans per member: only ids so far.
SELECT member_id, count(*) AS loans
FROM loans
GROUP BY member_id
ORDER BY member_id;

test.sql

-- test: Ada, Grace and Linus, each with loans, open loans and the date of the last loan
-- output:
--  name  | loans | open | last_loan
-- -------+-------+------+------------
--  Ada   |     2 |    1 | 2026-09-12
--  Grace |     2 |    1 | 2026-09-18
--  Linus |     1 |    1 | 2026-09-15
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A column that is neither grouped nor aggregated

SELECT genre, title, count(*)
FROM books
GROUP BY genre;

What psql prints

ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an aggregate function

Why, and the fix

The sci-fi group holds three titles, and its one result row has room for one value. PostgreSQL will not pick one for you. Either group by title too (then every book is its own group, and the counts are all 1), or put title inside an aggregate: min(title) for one of them, or count(title) for how many.

Adding up text

SELECT genre, sum(title)
FROM books
GROUP BY genre;

What psql prints

ERROR:  function sum(text) does not exist

Why, and the fix

sum and avg exist only for number types (and intervals), so there is no sum of a text column. To count the titles, write count(title); for the first title in alphabetical order, min(title). Casting will not help here: the titles are not numbers.

An aggregate inside an aggregate

SELECT max(count(*))
FROM loans
GROUP BY member_id;

What psql prints

ERROR:  aggregate function calls cannot be nested

Why, and the fix

The aim was the highest number of loans per member, but an aggregate’s argument may not contain another aggregate. Compute the counts per group and sort them: SELECT member_id, count(*) AS loans FROM loans GROUP BY member_id ORDER BY loans DESC LIMIT 1. Two members tie here with 2, so add a second sort key to decide which one comes first.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

An aggregate turns many rows into one value

count(*) counts rows. count(returned_on) counts only the rows where returned_on is not NULL, and count(DISTINCT member_id) counts different values: five loans, two returned, three borrowers. sum, avg, min and max skip NULLs too. Watch the result types: count(*) and the sum of an integer column are bigint, and the avg of an integer column is numeric with many decimals, 1.5000000000000000; round(avg(copies), 2) shows 1.50. min and max work on numbers, text and dates, but sum and avg need numbers: sum(title) fails with function sum(text) does not exist.

GROUP BY makes one row per group

GROUP BY genre puts the rows with the same genre into one group, and the query returns one row per group, with each aggregate computed over that group’s rows: four genres, four rows. With GROUP BY genre, copies, a group is each different pair, so sci-fi splits into two rows. Rows whose grouping column is NULL form one group of their own. After grouping, every column in the select list must be grouped or sit inside an aggregate: a genre has three titles, and PostgreSQL will not pick one. Joins come first: join loans to books, then GROUP BY b.genre counts the loans per genre.

No rows, and the order of groups

Without GROUP BY, an aggregate query returns exactly one row, even when WHERE leaves no rows at all: count(*) is then 0, while sum, avg, min and max are NULL, not 0. Write coalesce(sum(copies), 0) when a report needs a 0. Groups come back in no promised order, so add ORDER BY. You can sort by an aggregate, ORDER BY count(*) DESC, or by its alias, ORDER BY books DESC, with a second key such as genre so that ties keep a fixed order. Aggregates cannot be nested: max(count(*)) fails. Sort the counts and keep the first row with LIMIT 1 instead.

Sources

Last reviewed September 30, 2026