SQL window functions and GROUP BY with running totals

A school reading club records each reading session. Its captain wants two views: a short summary showing each student's total pages, and a detailed timeline showing how the total grew after every session.

Those views need different query shapes. GROUP BY makes a summary row for each group. A window calculation can add information while keeping the individual session rows. We will build both in SQLite, then handle a subtle problem: two sessions recorded on the same day.

Before you begin, you should be comfortable reading a simple SELECT query. We will explain the new window-function words as we use them.

Create a small session log

Run these blocks in order in a fresh SQLite database with window-function support:

CREATE TABLE reading_sessions (
    session_id INTEGER PRIMARY KEY,
    student TEXT NOT NULL,
    day TEXT NOT NULL,
    pages INTEGER NOT NULL
);

INSERT INTO reading_sessions VALUES
    (1, 'Mira', '2026-10-01', 12),
    (2, 'Mira', '2026-10-02', 8),
    (3, 'Mira', '2026-10-02', 15),
    (4, 'Dev',  '2026-10-01', 10),
    (5, 'Dev',  '2026-10-03', 20);

The dates use a consistent year-month-day format, so their text order matches their calendar order. The unique session_id also gives us an explicit rule for ordering sessions with the same date. In this example, the smaller ID comes first.

Collapse the log into a summary

SELECT student, SUM(pages) AS total_pages
FROM reading_sessions
GROUP BY student
ORDER BY student;

This produces two rows: Dev has 30 pages and Mira has 35. Five session rows have become two student summaries. SQLite's SELECT documentation explains this one-result-row-per-group behavior.

That is perfect for a leaderboard, but it no longer shows Mira's separate sessions. Adding a random session ID to the selected columns would not reconstruct the missing timeline. Decide the level of detail you want before deciding which columns to display.

Keep the sessions and calculate a running total

SELECT student, session_id, day, pages,
       SUM(pages) OVER (
           PARTITION BY student
           ORDER BY day, session_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_pages
FROM reading_sessions
ORDER BY student, day, session_id;

The result preserves all five sessions:

student | session_id | day        | pages | running_pages
Dev     | 4          | 2026-10-01 | 10    | 10
Dev     | 5          | 2026-10-03 | 20    | 30
Mira    | 1          | 2026-10-01 | 12    | 12
Mira    | 2          | 2026-10-02 | 8     | 20
Mira    | 3          | 2026-10-02 | 15    | 35

PARTITION BY student starts a separate calculation for each student. The inner ORDER BY defines the session sequence. The explicit ROWS frame includes every row from that student's first session through the current one. The outer ORDER BY controls how the finished result is displayed. These are separate jobs. See SQLite's window-function documentation.

Follow Mira's rows with a pencil: start with 12, add 8 to reach 20, then add 15 to reach 35. Dev's pages never enter Mira's calculation because they belong to a different partition.

The final total for each student agrees with the earlier summary, which gives us a useful check. If Mira's last running total were 47, we would investigate the calculation rather than accepting a nicely formatted table. Small examples are valuable because you can verify every addition yourself.

Make tied dates intentional

Why spell out both the frame and the tie-breaker? If we instead use SUM(pages) OVER (PARTITION BY student ORDER BY day), SQLite's default frame includes peers sharing the current ordering value. Both October 2 rows then show 35, rather than 20 followed by 35. That is useful for a day-level cumulative total, but it answers a different question. SQLite documents the default frame here.

Choosing ROWS expresses row-by-row accumulation. Including the unique session_id makes the order among tied dates deterministic. Without that tie-breaker, merely choosing ROWS does not establish which same-day session comes first.

Try it yourself

Mira records session 6 on October 3 with five pages. How many rows will each query return, and what is her final running total?

Answer: the grouped query still returns two rows. The window query returns six rows. Mira's new running total is 40; Dev remains at 30.

Choose the summary when readers need one answer per student. Choose the window when they need to inspect each session and its context. Being clear about the output rows is often the fastest way to choose the right SQL.