MySQL Adding & Multiple Columns On Select
<p>My database looks as follows</p>

<p>Table Name: Order Details </p>

<pre><code>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
</code></pre>

<p>Table Name: Orders</p>

<pre><code>id       odiscount oshipping
----------------------------
  1       5       5        
  2       0       5        
  3       0       5  
</code></pre>

<p>I want to get the invoice value for each order. <code>(pquantity*pprice)-odiscount+oshipping</code>. 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</p>

<pre><code>oid   total
1     55
2     15
3     55
</code></pre>

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

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