MySQL Adding & Multiple Columns On Select

Question

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

Tim Biegeleisen · Accepted Answer · 2016-10-05 07:05:36Z

3

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

edited Oct 5, 2016 at 7:05

answered Oct 5, 2016 at 6:59

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

I agree that this is probably the way to go, but I'm very puzzled by the attitude towards comments.

Tim Biegeleisen Over a year ago

@Strawberry Sagi routinely dumps a ton of comments on my answers, creating a conversation over nothing, with the hopes that his answer will appear more pristine, and therefore more correct, than everyone else's. Commentary: this tactic seems to work really well (though I don't use it).

sagi Over a year ago

LOL? First of all, you've tagged the person that comments on more answers than any of us(Which nothing wrong with that) . I didn't even answer questions for the past two weeks so rest assure that I don't have any routine of dumping comments and hoping my answer with be the accepted one. The reason my answers are accepted is only one - they are usually first, and when they are not, they wont get accepted. Chill man.

Mureinik · Accepted Answer · 2016-10-05 06:55:48Z

3

You could group the order_details query first, and only then join it on 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

answered Oct 5, 2016 at 6:55

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Collectives™ on Stack Overflow

MySQL Adding & Multiple Columns On Select

2 Answers 2

SQLFiddle

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related