Every customer with their total order amount, including those who ordered nothing
Tables customers(id, name) and orders(id, customer_id, amount). Return every customer with the total amount of their orders. Customers who have never ordered must still appear, with a total of 0 rather than being dropped or showing NULL.
-- one row per customer: name + total order amount, 0 when they never orderedWrite the query.
LEFT JOIN from customers to orders keeps customers with no order, then GROUP BY the customer and use COALESCE(SUM(o.amount), 0) so the NULL from unmatched rows becomes 0. An INNER JOIN would silently drop everyone who never ordered.
- ✗Using INNER JOIN and losing every customer who never ordered
- ✗Assuming SUM over the unmatched side already returns 0 without COALESCE
- ✗Adding a WHERE on the joined column that re-drops the zero-order customers
- →Why does SUM over the unmatched side return NULL rather than 0?
- →How would you also show the count of orders per customer in the same query?
LEFT JOIN keeps every customer, and COALESCE turns the NULL sum of unmatched rows into 0.
SELECT c.name,
COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total DESC;An INNER JOIN would drop customers with no matching order. And SUM over the NULL rows produced by the outer join returns NULL, not 0 — so COALESCE(SUM(...), 0) is what actually forces the zero for customers who never ordered.
