Description

Login
Back

Beginner · 12 min

Grouping rows

Produce one aggregate result per group.

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

Group before summarizing

GROUP BY creates one aggregate result for each grouping key. Select grouping keys and aggregates together. SQLite permits some ungrouped columns, but their values can be ambiguous; avoid relying on that behavior.

SELECT topic, SUM(minutes) AS minutes FROM sessions GROUP BY topic ORDER BY topic;

Output

topic | minutes
Food | 10
Music | 40
Travel | 65

Filter the input first

WHERE removes rows before groups are formed. Grouping completed sessions therefore summarizes only completed practice. An explicit ORDER BY controls how the groups are displayed.

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

Output

member_id | completed
1 | 1
2 | 2
3 | 1

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 total minutes per topic, with columns topic and minutes, sorted by topic.

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

SUM accumulates minutes inside each topic group.

Practice