SUM(a*b) not working...

Question

I have a PHP page running in postgres. I have 3 tables - workorders, *wo_parts* and part2vendor. I am trying to multiply 2 table column row datas together, ie *wo_parts* has a field called qty and part2vendor has a field called cost. These 2 are joined by *wo_parts.pn* and part2vendor.pn. I have created a query like this:

$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";

But if I add the costs of the parts multiplied by the qauntities supplied, it adds to a different number than what the script is doing. Help....I am new to this but if someone can show me in SQL I can modify it for postgres. Thanks

Answer 1

Hey.

You could try a sub-query instead.
(Note, I don't have a Postgres installation to test this on so consider this more like pseudo code than a working example... It does work in MySQL tho)

SELECT 
    SUM(p.`score`) AS 'total_score'
FROM part2vendor AS p2v
INNER JOIN (
    SELECT pn, cost * qty AS `score`
    FROM wo_parts
) AS p
    ON p.pn = p2v.pn
WHERE p2n.workorder=$workorder"

Answer 2

Without seeing example data, there's no way for us to know why you're query totals are coming out differently that when you do the math by hand. It could be a bad join, so you are getting more/less records than you expected. It's also possible that your calculations are off. Pick an example with the smallest number of associated records & compare.

My suggestion is to add a GROUP BY to the query:

  SELECT SUM(p.cost * wp.qty) as total_score 
    FROM part2vendor p
    JOIN wo_parts wp ON wp.pn = p.pn 
   WHERE workorder = $workorder
GROUP BY workorder

FYI: MySQL was designed to allow flexibility in the GROUP BY, while no other db I've used does - it's a source of numerous questions on SO "why does this work in MySQL when it doesn't work on db x...".

To Check that your Quantities are correct:

SELECT wp.qty,
       p.cost
  FROM WO_PARTS wp
  JOIN PART2VENDOR p ON p.pn = wp.pn
 WHERE p.workorder = $workorder

Check that the numbers are correct for a given order.

Answer 3

In the question, you say the cost column is in part2vendor, but in the query you reference wo_parts.cost. If the wo_parts table has its own cost column, that's the source of the problem.