Brightlane's wholesale team wants the total number of units sold on high-value line items — specifically, order lines where the per-unit price exceeded $500.
Write a query to return the total unit count in a single column named total_units.
Assumptions:
- The
order_itemstable contains one row per product per order. quantityis the number of units on the line;unit_priceis the per-unit price at time of purchase.- Only line items with
unit_price > 500should contribute to the total.
Output:
- A single row with one column,
total_units.
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
SUM(quantity) AS total_units
FROM
order_items
WHERE
unit_price > 500The shape
The filter narrows order_items to only the line items whose unit_price exceeds $500, and SUM then totals the quantity column across those filtered rows — the count of units sold at the premium tier. The answer is 39 units, across however many line items met the threshold.
Clause by clause
FROM order_itemsis the source: every line item across every order, one row per (order, product) combination.WHERE unit_price > 500filters first, keeping only line items where the unit price is strictly greater than $500. The comparison is exclusive:unit_price = 500does not pass. Premium-tier items, by Brightlane's working definition, sit above the threshold.SUM(quantity) AS total_unitsthen totals thequantitycolumn across the filtered rows. The filter is onunit_price, but the sum is onquantity.WHEREandSUMcan target different columns: the filter decides which rows count, andSUMdecides which column from those rows gets added.- The result is the number of premium-tier units sold. A line where 12 premium units were sold contributes 12 to the total; a line where 1 was sold contributes 1.
Why this and not SUM(unit_price) over the same filter
SUM(unit_price) FROM order_items WHERE unit_price > 500 would total the unit prices of premium line items — the dollar value of one of each, ignoring how many were sold. That tells you something about the price spread at the premium tier, not about units moved. Multiplying unit_price * quantity would be needed to get premium revenue, which is a different question again.
The team asked for unit volume at the premium tier, so the sum has to be on the volume column rather than the price column. The filter and the aggregate target different columns on purpose.
You practiced summing a column over a WHERE-filtered subset. The recurring shape: any time the question is "the total of X for the rows that meet condition Y," the answer is SUM(X) ... WHERE Y.
