Customer Totals with Zero Default — NULL Propagation in SQL

The problem

Scenario: Brightlane's finance team is producing a customer lifetime value report and needs every customer included — those who have never placed an order should appear with 0 rather than as missing.

Task: Write a query to return each customer's id, name, and total order value, with total_order_value reported as 0 for customers who have placed no orders.

Assumptions:

A customer's total_order_value is the combined total_amount across all of their orders.
The result covers every customer.
A customer with no orders on record appears with total_order_value reported as 0.

Output:

One row per customer.
Columns in this order: customer_id, customer_name, total_order_value.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  c.id AS customer_id,
  c.name AS customer_name,
  COALESCE(SUM(o.total_amount), 0) AS total_order_value
FROM
  customers c
  LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY
  c.id,
  c.name

The shape

The LEFT JOIN is what keeps every customer in the result, and COALESCE(SUM(o.total_amount), 0) is what converts the empty-side NULL into the 0 the lifetime-value report needs. A customer who has never placed an order arrives at the aggregation with no orders rows; SUM over an empty group returns NULL, not zero.

Clause by clause

SELECT c.id AS customer_id, c.name AS customer_name, COALESCE(SUM(o.total_amount), 0) AS total_order_value returns three columns per customer. The aggregate runs across whatever orders rows survived the join for that customer, and the COALESCE catches the NULL that arises only for customers with no orders at all.
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id pairs each customer with their orders. The LEFT JOIN preserves customers who have none, putting NULLs in every orders column for those rows.
GROUP BY c.id, c.name collapses the customer-and-orders rows back down to one row per customer so the SUM produces a per-customer total.

The trap

Wrap the SUM with COALESCE, not the column going into it. Writing SUM(COALESCE(o.total_amount, 0)) looks like the same idea, but for a customer with no orders there are zero rows to coalesce — SUM still sees an empty set and returns NULL. The substitution has to happen on the aggregate's output, where the NULL actually appears.

You practiced substituting 0 for the missing-value total that a left-join produces when a parent has no children — preventing the empty case from arriving as a missing value.

Return each customer's `id`, name, and total order value, with `total_order_value` reported as `0` for customers who have placed no `orders`

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.