Add all lines multiplied by another line in another table

I hope I can explain this good enough. I have 3 tables. wo_parts, work开发者_StackOverflow社区orders 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.

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.

The key cause I could see for something like this would be a type issue. For example, this could happen if you are using FLOATs instead of NUMERICs, you might get a slightly different answer. That is a mistake that is way too common, btw.

I would recommend double checking your schema to make sure you are using NUMERICs across the board here. NUMERIC is crazy-powerful on PostgreSQL, it performs very well, and it properly supports arbitrary precision operations. If you can't change the data type, cast your fields to numeric in your query.

FLOAT types (including DOUBLE) are fixed precision binary numbers, and they don't correspond exactly to base 10 numbers always. NUMERICs are stored internally as base 1000 (meaning 9 digits per 30 bits), and this is very efficient to convert to/from binary. The precision is also arbitrary, although it does have a maximum. However, for financial stuff, the maximum values or precision are not an issue with numeric data types. You should use them liberally.