Order Totals from a Derived Table — Query Performance in SQL

The problem

Scenario: Brightlane's fulfillment analytics team needs each order paired with the combined value of its line items, computed from line-item data rather than any pre-stored total.

Task: Write a query to return each order's id and the combined line-item value for every order that has at least one line item.

Assumptions:

A line item's value is quantity multiplied by unit_price.
An order's total_item_value is the combined line-item value across all of its line items.
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, total_item_value.

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,
  item_totals.total_item_value
FROM
  orders o
  JOIN (
    SELECT
      order_id,
      SUM(quantity * unit_price) AS total_item_value
    FROM
      order_items
    GROUP BY
      order_id
  ) AS item_totals ON item_totals.order_id = o.id

The shape

The per-order total has to come from order_items, but the output has one row per order. A derived table that aggregates order_items to one row per order_id first, then joins back to orders, keeps the aggregation step narrow and decoupled from the parent lookup.

Clause by clause

SELECT o.id AS order_id, item_totals.total_item_value returns the order's identifier alongside the per-order line-item total computed inside the derived table.
FROM orders o is the parent side. Every order appears here once.
The inner block reads order_items, computes SUM(quantity * unit_price) per order_id, and groups by order_id:

SELECT order_id, SUM(quantity * unit_price) AS total_item_value
FROM order_items
GROUP BY order_id

The result is one row per order that has at least one line item, with the combined value already summed. - AS item_totals ON item_totals.order_id = o.id joins the aggregated set back to orders on the matching key. Because the derived table holds only orders that had at least one line item, the inner join silently drops the orders with no line items, which is exactly the "at least one line item on record" qualifier the prompt asks for.

Why this and not an unaggregated join

Joining orders to order_items first and then grouping the result by o.id produces the same numbers, but the intermediate row count is the full line-item count rather than the order count. The derived-table shape keeps the intermediate set narrow, which is the structural choice the prompt is practicing.

You practiced precomputing per-order totals in a derived table before reattaching to the parent order — a structural choice that keeps the intermediate result narrow before the final shape comes together.

Return each order's `id` and the combined line-item value for every order that has at least one line item

The shape

Clause by clause

Why this and not an unaggregated join

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