Lesson 8 of 10

Running totals

12 min
5 checks
3 hints
Go to task

Goal

SUM with OVER

Explanation

A running total answers the question "How much have we sold up to this day?" Each row holds its own amount plus the amounts of all the rows before it.

SUM(total) OVER (ORDER BY placed_on) computes it without collapsing the rows into one the way GROUP BY does. The ORDER BY inside OVER sets the order of accumulation, so each row gets the sum of all the rows from the first one up to and including itself.

If you remove the ORDER BY from inside the parentheses, you'll see the same grand total on every row, because a window with no order covers the whole set. The ORDER BY at the end of the query only sorts how the rows are displayed, and it can't stand in for the window's order.

Your task

For each order, show placed_on, total and running, which is the running total up to that order, and sort the result by date.

Checks

5 checks

Columns are placed_on, total and running
One row per order
Sorted by date
Each row adds its order's amount to the total before it
The last row equals total sales

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

Running totals

Explanation

Your task

Checks

Hints