Description

Login
Back

Intermediate · 18 min

Filtering grouped results

Separate row filters from aggregate filters.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Filter groups with HAVING

WHERE runs before grouping; HAVING filters the groups after aggregation. Use HAVING for a condition on an aggregate. This query keeps only topics whose combined duration meets the threshold.

SELECT topic, SUM(minutes) AS minutes FROM sessions GROUP BY topic HAVING SUM(minutes) >= 40 ORDER BY topic;

Output

topic | minutes
Music | 40
Travel | 65

Use both filters

A query can restrict the input rows and then restrict the resulting groups. Here unfinished sessions never enter the aggregates, and only members with at least two completed sessions remain.

SELECT member_id, COUNT(*) AS completed FROM sessions WHERE completed = 1 GROUP BY member_id HAVING COUNT(*) >= 2 ORDER BY member_id;

Output

member_id | completed
2 | 2

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Return topics whose total minutes are at least 40, with columns topic and minutes.

Show solution
SELECT topic, SUM(minutes) AS minutes FROM sessions GROUP BY topic HAVING SUM(minutes) >= 40 ORDER BY topic;

HAVING checks the sum after the topic groups have been formed.

Practice