I got this question as part of a skills evaluation test. It requires a query to aggregate data from 2 tables in a one-to-many relationship in an “order system”-type database.
- There is an
`orders`table that consists of an
- Also, an
`order_items`table that consists of
unit_priceis the price of an individual order item and total is
unit_price * qty.
- Write a SQL query that will display all orders with the following fields listed (where the
total_valuefield is the sum of all of the order items totals for each order:
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`;