Brightlane's pricing team wants to analyse each order's line items by price tier. Per order:
- Total value of high-price line items (
unit_price > $500). - Total value of standard line items (
unit_price <= $500).
Line value is unit_price * quantity.
Write a query to return three columns per order: the order ID, the high-value total, and the standard total.
Assumptions:
- The
order_itemstable contains one row per product per order. - Each conditional total combines the per-line value (
unit_price * quantity), not justunit_price. - The two tiers are mutually exclusive — every line item contributes to exactly one of the two columns.
Output:
- One row per order with at least one line item, with columns
order_id,high_value_total, andstandard_total.
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
order_id,
SUM(
CASE
WHEN unit_price > 500 THEN unit_price * quantity
END
) AS high_value_total,
SUM(
CASE
WHEN unit_price <= 500 THEN unit_price * quantity
END
) AS standard_total
FROM
order_items
GROUP BY
order_idThe shape
The value inside the THEN branch is unit_price * quantity — a per-line revenue calculation, not a stored column. SUM(CASE WHEN cond THEN unit_price * quantity END) computes that per-line value on the rows the predicate admits and totals them inside each order's group. Order 36 has a $1,999 high-value line and $498.99 of standard lines; order 71 has only standard lines, so the high-value column comes back as NULL.
Clause by clause
order_idis the grouping column; each order's line items form one group.SUM(CASE WHEN unit_price > 500 THEN unit_price * quantity END) AS high_value_totalchecks each line'sunit_priceagainst the threshold. Lines above$500contribute their per-line revenue (unit_price * quantity); lines at or below$500fall through toNULLandSUMskips them.SUM(CASE WHEN unit_price <= 500 THEN unit_price * quantity END) AS standard_totalcovers the complement. The<=is the boundary-inclusive operator — a line priced at exactly$500lands in the standard bucket.FROM order_items GROUP BY order_idpartitions the rows per order before the aggregates run.
Why the computed value goes inside the THEN
The CASE branch can return any expression, not just a column reference. Computing unit_price * quantity inside THEN keeps the multiplication on the per-line input, then lets SUM total the per-line revenues across the group. Writing SUM(CASE WHEN cond THEN unit_price END) * SUM(CASE WHEN cond THEN quantity END) would multiply two group-level totals instead of summing the per-line products — a different number entirely.
The trap
The NULL results read as missing data, but they're a structural signal: that order has no line items in that bucket. A tier-mix report needs to read NULL as "zero lines at this tier," not "zero revenue at this tier." If the report needs zero-padding, adding ELSE 0 inside each CASE makes non-matched rows contribute zero and the totals come back as 0 instead of NULL.
You practiced SUM(CASE WHEN cond THEN expression END) where the value being summed is itself a calculation. The recurring shape: any per-line computed metric (revenue, weight, taxable amount) can be conditionally bucketed in the same one-pass aggregation.
