Lesson 3 of 10

Totals per customer

13 min
5 checks
3 hints
Go to task

Goal

Combining JOIN with GROUP BY

Explanation

You answer "How much did each customer spend?" in two steps. First you join customers to their orders, then you gather each customer's rows with GROUP BY customers.id and compute SUM(orders.total). We group by id here, not by name, because names can repeat.

To include the customers who haven't ordered anything, use LEFT JOIN as in the previous lesson.

But SUM returns NULL, not zero, when all of its values are NULL, so wrap it in COALESCE(SUM(orders.total), 0). COALESCE returns the first value you pass it that isn't NULL.

Your task

Show name and spent for every customer, where spent is the sum of the customer's orders, or zero for customers with no orders. Sort them from the biggest spender to the smallest.

Checks

5 checks

Columns are name and spent
Every customer is listed
Customers with no orders get zero, not NULL
Each customer's total is correct
Sorted biggest spender first

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

Totals per customer

Explanation

Your task

Checks

Hints