Skip to content
aviral gupta
Get in touch

// B3.6 · ~30 min · Beginner

Build: a loans report

After this build you can write a report that keeps every member, counts what each one has borrowed and still has, and check its totals against the data.

Lesson 6 of 6 in B3 Joins and aggregates

End of the module

You will be able to

  • Keep every member in a report with LEFT JOIN, and count their loans with count(l.id)
  • Count open loans with FILTER and find each member’s last loan date with max
  • Report per genre, find never-borrowed books with an anti-join, and check the totals

  1. Warm-up · Activity 1 of 7

    Warm-up: the report must show every member, also those who have never borrowed a book. How should its FROM part start?

  2. Predict · Activity 2 of 7

    Predict before you read on. Margaret has never borrowed a book. What does the loans column show in her row?

    SELECT m.name, count(*) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

  3. Practice · Activity 3 of 7

    Fill in the argument of count so that Margaret’s row shows 0 loans.

    SELECT m.name, count(____) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name
ORDER BY m.name;
    SELECT m.name, count() AS loans FROM members m LEFT JOIN loans l ON l.member_id = m.id GROUP BY m.id, m.name ORDER BY m.name;

  4. Practice · Activity 4 of 7

    Match each column of the report to the expression that computes it.

  5. Practice · Activity 5 of 7

    Put the lines of the per-member report in the order SQL requires.

    1. 1.ORDER BY m.name;
    2. 2.GROUP BY m.id, m.name
    3. 3.FROM members m
    4. 4.SELECT m.name, count(l.id) AS loans
    5. 5.LEFT JOIN loans l ON l.member_id = m.id

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Margaret has no loans at all. What does the open column show in her row?

    SELECT m.name,
       count(*) FILTER (WHERE l.returned_on IS NULL) AS open
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

  7. Apply · Activity 7 of 7

    Mini-task. Write the report per city: for each city, the number of members, their loans and their open loans, with every member counted, also Margaret, whose city is NULL (show it as unknown). Predict first what the Berlin row says, then check the loans column adds up to 5.

    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

A report per book, and its check

setup.sql loads the library of module B1: books, members and loans. main.sql builds the report per book: how often each book was lent, how many copies are still out, and the last loan date. Then it lists the books nobody has borrowed and the totals to check the report against. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Every book, lent or not: times lent, still out, last loan.
SELECT b.title,
       count(l.id) AS times_lent,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS still_out,
       max(l.loaned_on) AS last_loan
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title
ORDER BY b.id;

-- 2. The books nobody has borrowed yet.
SELECT b.title
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
WHERE l.id IS NULL
ORDER BY b.title;

-- 3. The totals, to check the report against.
SELECT count(*) AS loans,
       count(*) FILTER (WHERE returned_on IS NULL) AS open,
       count(DISTINCT member_id) AS borrowers
FROM loans;

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

    title    | times_lent | still_out | last_loan
-------------+------------+-----------+------------
 Dune        |          2 |         1 | 2026-09-15
 Emma        |          1 |         0 | 2026-09-05
 Kindred     |          1 |         1 | 2026-09-12
 Beloved     |          0 |         0 |
 The Hobbit  |          0 |         0 |
 Neuromancer |          1 |         1 | 2026-09-18
(6 rows)

   title
------------
 Beloved
 The Hobbit
(2 rows)

 loans | open | borrowers
-------+------+-----------
     5 |    3 |         3
(1 row)
  • FROM books LEFT JOIN loans keeps Beloved and The Hobbit, and count(l.id) gives them 0 instead of 1.
  • GROUP BY b.id, b.title makes one row per book; ORDER BY b.id keeps the order of the catalogue.
  • max(l.loaned_on) is the latest loan date; a book without loans gets NULL, an empty cell.
  • The check: times_lent adds up to 5 and still_out to 3, the totals of the third query.
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

Step 1: the report per member

The starter joins loans to members with an inner join, so Margaret is missing, and it counts with count(*). Turn it into the report per member: every member, with name, loans (the number of loans), open (the loans not yet returned) and last_loan (the date of the latest loan), sorted by name.

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

    Start FROM members m and add LEFT JOIN loans l ON l.member_id = m.id, so Margaret stays.

  2. Hint 2

    Count a loans column, count(l.id), not count(*): Margaret’s NULL row must count as 0.

  3. Hint 3

    open is count(l.id) FILTER (WHERE l.returned_on IS NULL); last_loan is max(l.loaned_on).

Show a solution

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

SELECT m.name,
       count(l.id) AS loans,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS open,
       max(l.loaned_on) AS last_loan
FROM members m
LEFT JOIN loans l ON l.member_id = m.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

-- setup.sql has loaded books, members and loans.
SELECT m.name, count(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY m.name;

test.sql

-- test: Every member appears, Margaret with 0 loans, 0 open and no 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
--  Margaret |     0 |    0 |
-- (4 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

Step 2: the report per genre

Write two queries. First, the report per genre: every genre, with genre, books (the number of books, each counted once), loans and open, sorted by genre; start from books and add the loans with LEFT JOIN. Second, the genres whose books have never been borrowed, with the genre only, sorted by genre.

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

    FROM books b LEFT JOIN loans l ON l.book_id = b.id, then GROUP BY b.genre.

  2. Hint 2

    Dune has two loans, so it appears twice after the join: count(DISTINCT b.id) counts it once.

  3. Hint 3

    A genre with no loans has count(l.id) = 0; that test is about a group, so it goes in HAVING.

Show a solution

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

SELECT b.genre,
       count(DISTINCT b.id) AS books,
       count(l.id) AS loans,
       count(l.id) FILTER (WHERE l.returned_on IS NULL) AS open
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.genre
ORDER BY b.genre;

SELECT b.genre
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.genre
HAVING count(l.id) = 0
ORDER BY b.genre;
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, count(*) AS books
FROM books
GROUP BY genre
ORDER BY genre;

test.sql

-- test: The report per genre, then fantasy and fiction as the genres never borrowed
-- output:
--   genre  | books | loans | open
-- ---------+-------+-------+------
--  classic |     1 |     1 |    0
--  fantasy |     1 |     0 |    0
--  fiction |     1 |     0 |    0
--  sci-fi  |     3 |     4 |    3
-- (4 rows)
--
--   genre
-- ---------
--  fantasy
--  fiction
-- (2 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 selected column missing from GROUP BY

SELECT m.name, count(l.id) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id;

What psql prints

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

Why, and the fix

Each output row stands for a group, and PostgreSQL does not know that a member id has only one name here. List every selected column that is not inside an aggregate in GROUP BY: GROUP BY m.id, m.name. Keep m.id there too, so two members with the same name stay apart.

An unqualified column that both tables have

SELECT m.name, count(id) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

What psql prints

ERROR:  column reference "id" is ambiguous

Why, and the fix

members and loans both have a column id, so a bare id could mean either. Qualify it with the table alias. For the report it must be the loans column, count(l.id); count(m.id) would count Margaret’s row as 1 again.

An aggregate in WHERE to find members without loans

SELECT m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE count(l.id) = 0
GROUP BY m.id, m.name;

What psql prints

ERROR:  aggregate functions are not allowed in WHERE

Why, and the fix

WHERE runs on single rows, before the groups and their counts exist. Test the count in HAVING: GROUP BY m.id, m.name HAVING count(l.id) = 0. Or skip the grouping and use the anti-join: LEFT JOIN loans l … WHERE l.id IS NULL.

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

Start from the table whose rows must all appear

A report "per member" must list every member, also Margaret, who has never borrowed a book. So start FROM members and add the loans with LEFT JOIN loans l ON l.member_id = m.id: an inner join would drop her. Then GROUP BY m.id, m.name makes one row per member. Group by the id as well as the name, because two members could share a name; the name must be in GROUP BY too, or PostgreSQL rejects it in the select list. Finish with ORDER BY, so the report reads the same every time. The same plan works per book: FROM books LEFT JOIN loans, GROUP BY b.id, b.title.

Count a column of the optional table

For Margaret the LEFT JOIN adds one row with NULL in every loans column. count(*) counts that row, so it says 1. count(l.id) counts only the rows where l.id is not NULL, so it says 0, which is the right answer. The same trap waits in FILTER: count(*) FILTER (WHERE l.returned_on IS NULL) also counts Margaret’s empty row, because its returned_on is NULL too. Write count(l.id) FILTER (WHERE l.returned_on IS NULL) for the open loans. max(l.loaned_on) gives the last loan date; for Margaret it is NULL, which psql shows as an empty cell.

Check the report against the data

A report that runs can still be wrong. Compare it with what you know: five loans in all, three of them open, so the loans column must add up to 5 and the open column to 3. Joins multiply rows: after books LEFT JOIN loans, Dune appears twice, once per loan. count(DISTINCT b.id) then still counts it once, but sum(b.copies) would add its copies twice. Rows without a partner are the other check: LEFT JOIN loans … WHERE l.id IS NULL lists the books nobody has borrowed, here Beloved and The Hobbit. Run a small totals query next to the report.

Sources

Last reviewed October 3, 2026