Add all lines multiplied by another line in another table

Question

Hi, I hope I can explain this good enough. I have 3 tables. wo_parts, workorders and part2vendor. I am trying to get the cost price of all parts sold in a month. I have this script.

$scoreCostQuery = "SELECT SUM(part2vendor.cost*wo_parts.qty) as total_score 
                       FROM part2vendor 
                       INNER JOIN wo_parts 
                         ON (wo_parts.pn=part2vendor.pn)  
                       WHERE workorder=$workorder";

What I am trying to do is each part is in wo_parts (under partnumber [pn]). The cost of that item is in part2vendor (under part number[pn]). I need each part price in part2vendor to be multiplied by the quantity sold in wo_parts. The way all 3 tie up is workorders.ident=wo_parts.workorder and part2vendor.pn=wo_parts.pn. I hope someone can assist. The above script does not give me the same total as when added by calculator.

Answer 1

This is not an answer, just a comment.

Why don't you take the sum/multiply operation outside the SQL statement? I know, that seems stupid because it will increase the lines of code and the complexity of the script, but, imho, it is always a good thing to keep code and SQL statements as far away as possible.