Total Spend per Customer

SQLAggregationJuniorMidUpdated Nov 10, 2026

Return the total amount each customer has spent, one row per customer.

Schema

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  status       TEXT NOT NULL,
  total        NUMERIC NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id;

Every column in SELECT must be in GROUP BY or inside an aggregate. (a) has an aggregate but no GROUP BY, so Postgres rejects customer_id with 'must appear in the GROUP BY clause or be used in an aggregate function'. (c) fails the same way on status. (d) runs, but a window function keeps one row per order, and SUM(total) OVER () puts the grand total of the whole table on every row.

Going Deeper

Postgres allows one shortcut: when you GROUP BY a table's primary key, you can select that table's other columns without listing them, because they depend on the key. GROUP BY c.id with SELECT c.id, c.email works when customers.id is the primary key.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT customer_id, SUM(total) AS spent
FROM orders;

Option (c)

SELECT customer_id, status, SUM(total) AS spent
FROM orders
GROUP BY customer_id;

Option (d)

SELECT customer_id, SUM(total) OVER () AS spent
FROM orders;