Got this question as part of a skills evaluation test. It requires a query to calculate summary-like data from tables in an order system type database.
- There is an
`orders`
table that consists of anorder_no
andcustomer_id fields
. - Also, an
`order_items`
table that consists oforder_item_id
,order_id, product_id
,qty
,unit_price
andtotal
. unit_price
is the price of an individual order item and total isunit_price * qty
.- Write a SQL query that will display all orders with the following fields listed (where the
total_value
field is the sum of all of the order items totals for each order:order_no
,customer_id
,total_value
My tested solution:
SELECT `o`.`order_no`, `o`.`customer_id`, SUM(`oi`.`total`) AS `total_value` FROM `orders` `o` JOIN `order_items` `oi` ON `o`.`order_no` = `oi`.`order_id` GROUP BY `oi`.`order_id` ORDER BY `o`.`order_no`;