Count Rows After Joining Items — Join Fanout in SQL

The problem

Scenario: Brightlane's operations analyst is sizing the row volume of a planned line-item revenue report before committing to its compute cost.

Task: Write a query to return the total number of (order, line-item) pairings on record — each line item paired with its parent order.

Assumptions:

Every line item in order_items corresponds to exactly one parent order, and an order may have multiple line items.

Output:

One row, holding the total pairing count.
Columns in this order: joined_row_count.

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
  COUNT(*) AS joined_row_count
FROM
  orders o
  JOIN order_items oi ON oi.order_id = o.id

The shape

Joining orders to order_items on order_id produces one row per line item, not per order. COUNT(*) on that joined result returns the pairing count the operations team needs — every line item paired with its parent order, summed across the whole catalog.

Clause by clause

SELECT COUNT(*) AS joined_row_count counts every row produced by the join. Each row in that result represents one (order, line item) pairing, so the count is the pairing total for the planned report.
FROM orders o reads the order records and aliases the table as o. This is the parent side of the relationship.
JOIN order_items oi ON oi.order_id = o.id pairs each order with its line items. An order with five line items contributes five rows to the joined result; an order with one line item contributes one row.

Why this and not `COUNT(*) FROM orders`

Counting orders alone returns the number of orders, which is the smaller figure. The report's compute cost is driven by the joined row count, not the parent count, because the downstream aggregation runs over the joined result. The whole point of sizing here is to see how much the join inflates the row volume before committing to the cost — counting only the parent table hides that inflation entirely.

You practiced sizing a join's output by counting (parent, child) pairings — when each parent has multiple children, the pairing count exceeds the parent count.

Return the total number of (order, line-item) pairings on record — each line item paired with its parent order

The shape

Clause by clause

Why this and not `COUNT(*) FROM orders`

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

The shape

Clause by clause

Why this and not COUNT(*) FROM orders

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

Why this and not `COUNT(*) FROM orders`