Complex SQL query using SUM and GROUP BY

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 an order_no and customer_id fields.
Also, an order_items table that consists of order_item_id, order_id, product_id, qty, unit_price and total.
unit_price is 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_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`;

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`;

Complex SQL query using SUM and GROUP BY

Leave a Reply Cancel reply