Description

Login
Back

Beginner · 12 min

Aggregate functions

Summarize rows with COUNT, SUM, and AVG.

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

Summarize a set of rows

COUNT(*) counts rows, while SUM and AVG summarize non-null values. WHERE filters the input before aggregation. Without GROUP BY, the aggregate produces one summary row.

SELECT COUNT(*) AS sessions, SUM(minutes) AS minutes FROM sessions WHERE completed = 1;

Output

sessions | minutes
4 | 80

Count known values

COUNT(column) counts only non-null values. COUNT(*) includes every row. On an empty input, COUNT returns zero while SUM normally returns NULL. Do not treat those results as interchangeable.

SELECT COUNT(*) AS members, COUNT(city) AS known_cities FROM members;

Output

members | known_cities
4 | 3

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 the number of members and the number with a known city, using the aliases members and known_cities.

Show solution
SELECT COUNT(*) AS members, COUNT(city) AS known_cities FROM members;

Counting the column excludes the one missing city while counting all rows still includes that member.

Practice