mysql: how to sum multiplied partials

Question

Having tables like:

sold_items:

code   | size   | quantity 
abc123 | small  | 4
abc123 | medium | 2  
xyz987 | small  | 3
xyz987 | medium | 1
xyz987 | large  | 2

price_list:

code   | size   | price 
abc123 | small  | 5
abc123 | medium | 7  
abc123 | large  | 9 
xyz987 | small  | 10
xyz987 | medium | 13
xyz987 | large  | 15

which would be your best (faster query) approach to get

results:

code   | sold_items | cash
abc123 | 6          | 34
xyz987 | 6          | 73

Answer 1

Try this out please.

• SQLFIDDLE DEMO

Code:

select p.code, sum(p.quantity), sum(q.quantity * p.price)
from prc p
inner join 
qty q
on p.code = q.code
and p.size = q.size
group by p.code
;

Results:

CODE    SUM(Q.QUANTITY)     SUM(Q.QUANTITY * P.PRICE)
abc123      6                   34
xyz987      6                   73

Answer 2

This should work:

 SELECT si.code, SUM(si.quantity), SUM(si.quantity * pl.price) as cash
 FROM sold_items si
 INNER JOIN price_list pl ON pl.code = si.code AND pl.size = si.size
 GROUP BY si.code