Tough Mysql Query....

Question

Hi guys. I got a skill test for a quick mysql query. I am given the tables:

    Orders                    OrderItems
----------------------------------------                
    id                         id
    date                       order_id(Orders.id)
    shipping_amount            product_id
    order_status               price
    customer_id             quantity

I need to show Orders id and totals (including shipping amount) and records prior to June 1, 2003.

output should be....

| OrderID   | OrderTotal |
+-----------+------------+
| 13230     | $55.00     |
| 54455     | $40.00     |
| 59694     | $33.04     |
| 39495     | $21.05     |

The hint is to use Group by or subselect. I have the following statement so far but not sure what to do next. Any help will be appreciated...

Select id AS OrderId,***(not sure what to do here) 
from Orders join OrderItems on Orders.id=OrderItems.id 

Answer 1

I don't have access to a mysql database to test, but I would imagine it looks something like this:

select
  o.id OrderID,
 (select sum(oi.price * oi.quantity) from order_items oi where oi.order_id = o.id) + o.shipping_amount OrderTotal
from
  orders o
where
  o.date < str_to_date('2003-06-01', '%Y-%m-%d');