MySQL add and multiple columns to choose from

Question

My database is as follows

Table Name: Order Information

id oid pid pquantity pprice -------------------------------------- 1 1 5 2 10 2 1 6 3 5 3 1 7 1 20 5 2 8 1 5 6 2 9 1 5 7 3 5 5 10 

Table Name: Orders

 id odiscount oshipping ---------------------------- 1 5 5 2 0 5 3 0 5 

I want to get an invoice value for each order. (pquantity*pprice)-odiscount+oshipping . The tricky part is that each order can have multiple entries in the order details. Therefore, I cannot figure out how to take care of this. The end result should be

 oid total 1 55 2 15 3 55 

I tried this using the following SQL, but I cannot figure out how to take into account a few lines in detail.

 SELECT SUM((orderdetails.pprice*orderdetails.pquantity) - orders.odiscount + orders.oshipping) FROM orders LEFT JOIN orderdetails ON orderdetails.oid = orders.id GROUP BY orders.id 

Answer 1

I believe you can do this without even using a subquery:

 SELECT SUM(od.pquantity*od.pprice) + AVG(o.oshipping - o.odiscount) FROM Orders o INNER JOIN OrderDetails od ON o.id = od.oid GROUP BY o.id 

Demo here:

SQLFiddle

Answer 2

First you can group the order_details request and only then join it in the orders table:

 SELECT sum_details - odiscount + oshipping FROM orders o JOIN (SELECT oid, SUM(pquantity * pprice) AS sum_details FROM order_details GROUP BY oid) d ON o.id = d.oid