Saad Bashir Saad Bashir - 2 months ago 6
MySQL Question

MySQL Adding & Multiple Columns On Select

My database looks as follows

Table Name: Order Details

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 the invoice value for each order. (pquantity*pprice)-odiscount+oshipping. The tricky part is that each order can have multiple entries in order details. So I am unable to figure out how to take care of that. The end result should be

oid total
1 55
2 15
3 55


I tried this using the following SQL but I am unable to figure out how to take multiple rows in order details into account

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

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

Comments