Brightlane's merchandising team is reviewing product performance and needs a revenue summary by item.
Write a query to return each product's ID, the total units sold, and the total revenue across all order line items.
Assumptions:
- The
order_itemstable contains one row per product per order. quantityis the units on the line;unit_priceis the per-unit price at time of purchase.- Total revenue per line item is
quantity * unit_price; total revenue per product is the sum of that calculation across all of the product's line items.
Output:
- One row per
product_id, with columnsproduct_id,total_units, andtotal_revenue.
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
product_id,
SUM(quantity) AS total_units,
SUM(quantity * unit_price) AS total_revenue
FROM
order_items
GROUP BY
product_idThe shape
SUM(quantity * unit_price) computes the line total for each row first, then sums those line totals inside each product's group. The arithmetic happens once per row, and the aggregate then collapses the per-row results down to one number per product. Two aggregates in the same SELECT list mean two summary columns side by side, both partitioned by product_id.
Clause by clause
SELECT product_id, SUM(quantity) AS total_units, SUM(quantity * unit_price) AS total_revenuereturns three columns: the product, its total unit count, and its total revenue. BothSUMcalls run against the same group at the same time.product_idis the only plain column and is inGROUP BY.FROM order_itemsreads one row per product per order. A product that appears on ten different orders has ten rows in this table.GROUP BY product_idpartitions the line items by product. EachSUMthen runs independently inside each partition.
Why this and not summing quantity and unit_price separately
SUM(quantity) * SUM(unit_price) would multiply total units sold by total of every line's unit price, which is meaningless. The merchandising report needs the sum of (quantity times price) per line, not the product of two separate sums. Putting the multiplication inside the SUM is what keeps the per-row pairing intact. Product 6 has six units sold across orders that totaled 11994 in revenue. The unit price varied across those orders, and SUM(quantity * unit_price) adds up the actual per-line revenue regardless of which price was in force.
The trap
Mixing computed columns and aggregates inside SELECT works only when the computed column lives inside an aggregate or appears in GROUP BY. SUM(quantity * unit_price) is fine because the whole expression is wrapped in SUM. A bare quantity * unit_price in the SELECT list would fail, because neither quantity nor unit_price is in GROUP BY and the expression is not aggregated. The same grouping rule applies to expressions, not just to bare columns.
You practiced wrapping a computed expression (quantity * unit_price) inside an aggregate. The aggregate sees the per-row computation result and sums those — a one-pass alternative to producing the line totals first and summing them in a second query.
