Item Count per Order — Join Fanout in SQL

The problem

Scenario: Brightlane's operations team wants a complexity metric for each order — the count of line items it contains.

Task: Write a query to return each order's id and the number of line items it contains.

Assumptions:

The result covers only orders with at least one line item on record.

Output:

One row per order with at least one line item.
Columns in this order: order_id, item_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
  o.id AS order_id,
  COUNT(oi.id) AS item_count
FROM
  orders o
  JOIN order_items oi ON oi.order_id = o.id
GROUP BY
  o.id

The shape

COUNT(oi.id) on the joined result counts one row per line item per order, which is exactly what the complexity metric is — line items per order. Grouping by o.id then collapses those rows into one count per order.

Clause by clause

SELECT o.id AS order_id, COUNT(oi.id) AS item_count returns each order's ID and the count of its line items. COUNT(oi.id) counts non-null oi.id values inside each group, which equals the number of line items the order has.
FROM orders o reads the order records as the driving table.
JOIN order_items oi ON oi.order_id = o.id pairs each order with its line items. The inner join drops any order with zero line items, which matches the prompt's "covers only orders with at least one line item" constraint.
GROUP BY o.id collapses the multi-row-per-order joined result into one row per order, with the count computed inside each group.

Why this and not `COUNT(*)`

On an inner join with a guaranteed match on every line item, COUNT(*) and COUNT(oi.id) produce the same result here. The habit of counting a specific child-table column matters more on a LEFT JOIN, where a parent with zero line items still appears in the result with NULLs on the child side — COUNT(*) would count that row as one, but COUNT(oi.id) correctly returns zero. Writing COUNT(oi.id) even on the inner-join version keeps the pattern consistent and the intent explicit.

You practiced totaling child-row counts up to the parent — one line per order with the count of its associated line items.

Return each order's `id` and the number of line items it contains

The shape

Clause by clause

Why this and not `COUNT(*)`

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(*)

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

Why this and not `COUNT(*)`