Skip to content
aviral gupta
Get in touch

// B3.5 · ~30 min · Beginner

HAVING and FILTER

After this lesson you can keep only the groups that matter, count open and returned loans side by side, and list a group’s titles in one cell.

Lesson 5 of 6 in B3 Joins and aggregates

You will be able to

  • Choose WHERE for single rows and HAVING for groups, and fix an aggregate in WHERE
  • Keep only the groups you need with HAVING on count or sum
  • Count several subsets in one pass with FILTER, and join values with string_agg

  1. Warm-up · Activity 1 of 7

    Warm-up: a query groups the loans by member and counts them. Which clause keeps only the members with at least two loans?

  2. Predict · Activity 2 of 7

    Predict before you read on. The sci-fi books have 2, 1 and 1 copies; classic, fiction and fantasy have one book each, with 1, 1 and 3 copies. What does this return?

    SELECT genre, count(*)
FROM books
WHERE copies = 1
GROUP BY genre
HAVING count(*) > 1;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the query returns only the genres with more than one book.

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

  4. Practice · Activity 4 of 7

    Match each clause to what it filters or orders.

  5. Practice · Activity 5 of 7

    Five loans, three of them still open (returned_on is NULL). What does this return?

    SELECT count(*) FILTER (WHERE returned_on IS NULL) AS open,
       count(*) AS all_loans
FROM loans;

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Ada and Linus live in Berlin, Grace in Munich, and Margaret’s city is NULL. What does this return?

    SELECT string_agg(city, ', ' ORDER BY city)
FROM members;

  7. Apply · Activity 7 of 7

    Mini-task. With the library of the worked example, write one query over loans joined to books: for each genre, the number of loans, the number still open, and the lent titles in alphabetical order in one cell. Keep only the genres with at least two loans. Predict first which genres survive and why one title appears twice.

    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

Filtering groups and counting subsets

setup.sql loads the library of module B1: books, members and loans. main.sql keeps the genres with at least three copies, combines WHERE and HAVING, counts open and returned loans per member in one pass, and lists the titles of each genre. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Genres with at least three copies in all.
SELECT genre, count(*) AS books, sum(copies) AS copies
FROM books
GROUP BY genre
HAVING sum(copies) >= 3
ORDER BY genre;

-- 2. WHERE picks loans, HAVING picks members.
SELECT member_id, count(*) AS september_loans
FROM loans
WHERE loaned_on >= '2026-09-05'
GROUP BY member_id
HAVING count(*) >= 2
ORDER BY member_id;

-- 3. Several counts in one pass with FILTER.
SELECT m.name,
       count(*) AS loans,
       count(*) FILTER (WHERE l.returned_on IS NULL) AS open,
       count(*) FILTER (WHERE l.returned_on IS NOT NULL) AS returned
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY m.name;

-- 4. The titles of each genre in one cell.
SELECT genre, string_agg(title, ', ' ORDER BY title) AS titles
FROM books
GROUP BY genre
ORDER BY genre;

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

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

 member_id | september_loans
-----------+-----------------
         2 |               2
(1 row)

 name  | loans | open | returned
-------+-------+------+----------
 Ada   |     2 |    1 |        1
 Grace |     2 |    1 |        1
 Linus |     1 |    1 |        0
(3 rows)

  genre  |           titles
---------+----------------------------
 classic | Emma
 fantasy | The Hobbit
 fiction | Beloved
 sci-fi  | Dune, Kindred, Neuromancer
(4 rows)
  • HAVING sum(copies) >= 3 keeps fantasy (The Hobbit alone has 3) and sci-fi (2 + 1 + 1); the aggregate in HAVING is written out, not taken from the alias.
  • WHERE first drops Ada’s loan of 2026-09-01; of the four loans left, only member 2 has two, so HAVING keeps one group.
  • The three counts in the third query each see different rows: FILTER narrows the input of its own count only.
  • ORDER BY inside string_agg sorts the titles within each cell; the ORDER BY at the end sorts the rows.
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

Only the groups that matter

Write two queries. First: the genres with at least two copies in all, with genre and the total copies (copies), sorted by genre. Second: the members who borrowed at least two books, with name and the number of loans (loans), sorted by name; join loans to members.

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

    The condition is about a group’s total, so it goes in HAVING, between GROUP BY and ORDER BY.

  2. Hint 2

    Write the aggregate out in HAVING: HAVING sum(copies) >= 2. The alias copies is not known there.

  3. Hint 3

    For the members: JOIN members m ON m.id = l.member_id, GROUP BY m.id, m.name, HAVING count(*) >= 2.

Show a solution

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

SELECT genre, sum(copies) AS copies
FROM books
GROUP BY genre
HAVING sum(copies) >= 2
ORDER BY genre;

SELECT m.name, count(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
HAVING count(*) >= 2
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 genre, sum(copies) AS copies
FROM books
GROUP BY genre
ORDER BY genre;

test.sql

-- test: fantasy and sci-fi have at least two copies, then Ada and Grace have two loans each
-- output:
--   genre  | copies
-- ---------+--------
--  fantasy |      3
--  sci-fi  |      4
-- (2 rows)
--
--  name  | loans
-- -------+-------
--  Ada   |     2
--  Grace |     2
-- (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.

Exercise 2 of 2

Open, returned and which titles

The starter counts each member's loans. Replace the single count with two: open (no returned_on) and returned, using FILTER. Then add titles: the titles the member borrowed, in alphabetical order, separated by a comma and a space. Join books for the titles. Keep the sort 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

    count(*) FILTER (WHERE l.returned_on IS NULL) AS open counts only the open loans of each member.

  2. Hint 2

    The returned loans are the ones where l.returned_on IS NOT NULL.

  3. Hint 3

    JOIN books b ON b.id = l.book_id, then string_agg(b.title, ', ' ORDER BY b.title) AS titles; the ORDER BY goes after the separator.

Show a solution

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

SELECT m.name,
       count(*) FILTER (WHERE l.returned_on IS NULL) AS open,
       count(*) FILTER (WHERE l.returned_on IS NOT NULL) AS returned,
       string_agg(b.title, ', ' ORDER BY b.title) AS titles
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_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: one count so far.
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: Each member with open and returned loans and the borrowed titles in alphabetical order
-- output:
--  name  | open | returned |      titles
-- -------+------+----------+-------------------
--  Ada   |    1 |        1 | Dune, Kindred
--  Grace |    1 |        1 | Emma, Neuromancer
--  Linus |    1 |        0 | Dune
-- (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

An aggregate in WHERE

SELECT genre, sum(copies)
FROM books
WHERE sum(copies) >= 3
GROUP BY genre;

What psql prints

ERROR:  aggregate functions are not allowed in WHERE

Why, and the fix

WHERE decides which rows go into the groups, so it runs before any sum exists. A condition on a group’s total belongs in HAVING, after GROUP BY: GROUP BY genre HAVING sum(copies) >= 3. Conditions on single rows, such as year > 1970, stay in WHERE.

An output alias in HAVING

SELECT genre, count(*) AS n
FROM books
GROUP BY genre
HAVING n > 1;

What psql prints

ERROR:  column "n" does not exist

Why, and the fix

The alias n is given by the select list, which is computed after HAVING, so HAVING does not know it. Repeat the aggregate: HAVING count(*) > 1. ORDER BY n would work, because ORDER BY runs after the select list. An alias that is also a table name is worse: HAVING books > 1 fails with operator does not exist: books > integer, because books then means the table’s whole row.

string_agg over numbers

SELECT genre, string_agg(year, ', ')
FROM books
GROUP BY genre;

What psql prints

ERROR:  function string_agg(integer, unknown) does not exist

Why, and the fix

string_agg joins text (or bytea), and year is an integer. Cast the values to text first: string_agg(year::text, ', ' ORDER BY year). Sorting by year itself keeps the numbers in numeric order, so 1965 comes before 1979 and 1984.

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

WHERE filters rows, HAVING filters groups

A grouped query works in steps: FROM and the joins build the rows, WHERE drops single rows, GROUP BY forms the groups and computes the aggregates, HAVING drops whole groups, and then SELECT and ORDER BY run. So WHERE cannot use an aggregate, because no group exists yet: aggregate functions are not allowed in WHERE. HAVING count(*) >= 2 keeps the groups with at least two rows. One query can use both: WHERE copies = 1 first removes the books with more copies, then HAVING count(*) > 1 keeps the genres that still have two. A condition on single rows belongs in WHERE, even where HAVING would accept it.

In HAVING, write the aggregate out

HAVING runs before SELECT computes its output columns, so an alias from the select list is unknown there: HAVING n > 1 fails with column "n" does not exist. Write the aggregate again: HAVING count(*) > 1. ORDER BY runs last and may use the alias. The aggregate in HAVING need not be one you select: SELECT genre … HAVING sum(copies) >= 3 is fine. HAVING can test a grouped column too, HAVING genre <> 'sci-fi', but WHERE does that more cheaply. Without GROUP BY, HAVING tests the one whole-table group, and when the test fails, the query returns no row at all, not a 0.

FILTER counts subsets, string_agg lists values

count(*) FILTER (WHERE returned_on IS NULL) counts only the open loans, while a plain count(*) next to it still counts them all: FILTER removes rows from the input of its own aggregate only. So one pass over the loans gives the total, the open and the returned ones side by side, per member or per genre. string_agg(title, ', ') joins a group’s text values into one string, with the separator between them, and skips NULLs. Their order is unspecified unless you write ORDER BY inside the call, after all the arguments: string_agg(title, ', ' ORDER BY title). It takes text; cast a number first, year::text.

Sources

Last reviewed September 30, 2026