Scenario: Brightlane's finance team suspects an order revenue report is inflated because pairing orders with order_items is multiplying the result size. To verify, the analyst wants the count of pairings alongside the total order count for comparison.
Task: Write a query to return two counts: the total number of (order, line-item) pairings on record (joined_row_count), and the total number of orders (order_count).
Assumptions:
- Every line item corresponds to exactly one parent order; an order may have multiple line items.
- The
joined_row_countis the count of (order, line-item) pairings — each line item paired with its parent order. - The
order_countis the count oforders.
Output:
- One row, holding the two counts.
- Columns in this order:
joined_row_count,order_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
COUNT(*) AS joined_row_count,
(
SELECT
COUNT(*)
FROM
orders
) AS order_count
FROM
orders o
JOIN order_items oi ON oi.order_id = o.idThe shape
Two counts on one row, with the joined count produced by the outer join and the parent count produced by an independent scalar subquery against orders. Side by side, the gap between them is the size of the fanout.
Clause by clause
SELECT COUNT(*) AS joined_row_countcounts every row in the joined (order, line-item) result. Because an order can have multiple line items, this count grows past the order count whenever any order has more than one line item attached.(SELECT COUNT(*) FROM orders) AS order_countis a scalar subquery that runs against theorderstable on its own. It returns the unmultiplied parent count and gets dropped into the outer result as a second column.FROM orders o JOIN order_items oi ON oi.order_id = o.idpairs each order with its line items. Orders with no line items drop out, orders with one line item appear once, orders with three line items appear three times. The reference row shows 100 pairings against 200 orders, which means many orders have no line items at all and the join is filtering them out rather than multiplying.
Why this and not a single COUNT over the join
A single COUNT(*) on the joined result is the inflated number on its own. Without the parent count on the same row, the analyst still has to run a second query to interpret what 100 means. Pinning both numbers on one row is the diagnostic move — the comparison is what reveals fanout, not the joined count by itself.
You practiced sizing fanout by counting (parent, child) pairings against the parent count — when the pairing count exceeds the parent count, summing any parent-level value over the pairing set produces inflated totals.
