Brightlane's CRM team needs a complete customer activity view that shows the number of orders each customer has placed.
Write a query to return every customer's ID, name, and total order count.
Assumptions:
- A customer's order count is the number of orders linked to that
customer_id. - Every customer must appear, including customers with no orders. Customers with no orders should show an order count of
0.
Output:
- One row per customer, with columns
id,name, andorder_count.
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
c.id,
c.name,
(
SELECT
COUNT(*)
FROM
orders o
WHERE
o.customer_id = c.id
) AS order_count
FROM
customers cThe shape
The order count belongs to each customer row, so the count goes inline in the \SELECT\ list as a correlated scalar subquery. For every customer, the inner query runs once against \orders\ filtered to that customer's \id\, and \COUNT(*)\ returns the per-customer total. A customer with no matching orders gets \0\ for free, which is exactly the spec.
Clause by clause
- \
SELECT c.id, c.name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count\returns the customer's id and name, then a third column computed by the inner query. The inner \WHERE o.customer_id = c.id\references \c.id\from the outer row, which is what makes the subquery correlated. \COUNT(*)\over zero rows returns \0\, not NULL, so the count column is always populated. - \
FROM customers c\reads every customer record. There is no \WHERE\on the outer query because every customer is required in the output, including the ones who have never placed an order.
Why this and not \LEFT JOIN\ plus \GROUP BY\
A \LEFT JOIN customers c LEFT JOIN orders o ON o.customer_id = c.id\ followed by \GROUP BY c.id, c.name\ and \COUNT(o.id)\ produces the same numbers. The correlated form is shorter, keeps the customer row intact, and does not require listing every selected column in \GROUP BY\. When the only thing you need from the related table is one scalar per outer row, the correlated subquery is the more direct expression.
You practiced a correlated scalar subquery in the SELECT list — the inner COUNT(*) is re-evaluated per outer row, attaching a per-customer count to every record without a join.
