Scenario: Brightlane's operations team is doing a value reconciliation and needs item-computed revenue for every order, including orders with no recorded line items — treating those orders as carrying 0 item revenue rather than as missing.
Task: Write a query to return each order's id and its item_revenue — the combined revenue across its line items, reported as 0 for orders with no recorded line items.
Assumptions:
- A line item's revenue is
quantitymultiplied byunit_price. - An order's
item_revenueis the combined line-item revenue across all of its line items, reported as0when no line items are recorded against the order.
Output:
- One row per order present in the data, including orders with no line items.
- Columns in this order:
order_id,item_revenue.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
o.id AS order_id,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS item_revenue
FROM
orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY
o.idThe shape
LEFT JOIN keeps every order in the result, and COALESCE(SUM(...), 0) rewrites the NULL that SUM returns for a fully-NULL group into the 0 the reconciliation requires. The two pieces work together — the left join makes line-item-less orders appear, and the COALESCE makes their reported revenue read as a zero rather than as missing.
Clause by clause
SELECT o.id AS order_id, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS item_revenuereturns each order's ID and its line-item revenue. For an order with line items,SUMreturns the real total. For an order with none, everyoicolumn on its joined row is NULL,quantity * unit_priceis NULL, andSUMover a group of all-NULLs returns NULL — which theCOALESCEthen rewrites to0.FROM orders oreads every order, including those without line items. Preserving every order is the whole point of the reconciliation.LEFT JOIN order_items oi ON oi.order_id = o.idattaches line items where they exist and fills in NULLs where they don't. Orders with line items get one joined row per line item; orders without get one joined row with NULLs on theorder_itemsside.GROUP BY o.idcollapses the joined rows to one per order. The aggregation runs inside each group.
Why COALESCE and not letting NULL through
The audit is a value reconciliation, which means the team is comparing item-computed revenue against an order-level revenue figure elsewhere. A NULL in the reported column would not equal zero in any comparison; NULL = 0 evaluates to NULL, not TRUE, and an order showing a missing item-revenue value next to a real order-level value reads as a data gap rather than as a zero-line-item match. Substituting 0 makes the comparison arithmetic land cleanly and keeps every order on the same numeric scale.
The trap
The instinct on a LEFT JOIN with an aggregate is to put the COALESCE around the multiplication: SUM(COALESCE(oi.quantity * oi.unit_price, 0)). That works on this data, because SUM ignores NULLs anyway and a group of all zeros sums to zero. The cleaner pattern is to leave the per-row expression alone and wrap the SUM itself, because that names the actual substitution being made — "if the aggregate has no rows to total, report zero" — at the level where it matters. Both shapes return the right number here, but COALESCE(SUM(...), 0) is the one that scales to every other "aggregate with a default" reconciliation the team will write.
You practiced left-joining children to parents and substituting 0 for the missing total, so every parent carries a numeric value rather than a missing one.
