Description

Login
Back

Advanced · 18 min

Conditional aggregation

Compute several filtered measures in one grouped query.

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');

Add only matching values

A CASE expression inside SUM can turn unmatched rows into zero. This keeps several metrics in one grouped result. Consider how missing values and empty groups should be represented before choosing the ELSE branch.

SELECT member_id, SUM(CASE WHEN completed = 1 THEN minutes ELSE 0 END) AS completed_minutes FROM sessions GROUP BY member_id ORDER BY member_id;

Output

member_id | completed_minutes
1 | 20
2 | 45
3 | 15

Calculate a rate safely

Use a real operand to avoid integer division and NULLIF to avoid dividing by zero. The left join keeps members without activity; their undefined completion rate remains NULL instead of pretending to be a measured zero.

SELECT m.name, 1.0 * SUM(CASE WHEN s.completed = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(s.id), 0) AS rate FROM members AS m LEFT JOIN sessions AS s ON s.member_id = m.id GROUP BY m.id, m.name ORDER BY m.id;

Output

name | rate
Ada | 0.5
Bo | 1
Cy | 0.5
Dee | NULL

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 completed minutes per member, using zero for each unfinished session before summing.

Show solution
SELECT member_id, SUM(CASE WHEN completed = 1 THEN minutes ELSE 0 END) AS completed_minutes FROM sessions GROUP BY member_id ORDER BY member_id;

Only completed sessions contribute their duration to the total.

Practice