After joining tables SUM() function returns wrong value

General Discussion
1

I have two tables (postgres database)-
table1 has three columns (part_number, scrap_code, scrap_lbs)
table2 has three columns (fk_part_number, fk_scrap_code, operation_code)

When I SUM() the scrap_lbs column from table1, without joining table2; I get the expected values.
But, I need to have the operation_code in the resulting query.
When I JOIN table2, it appears to multiple the sum of the scrap_lbs by the number of rows with the operation_code. (So, I know where the larger value is being derived).

Here is my code:

SELECT part_number, scrap_code, SUM(scrap_lbs) AS Total, operation_code
FROM table1

JOIN table2
ON table1.part_number = table2.part_number

WHERE operation_code = (SELECT operation_code FROM table2 WHERE
fk_scrap_code = scrap_code AND fk_part_number = part_number GROUP BY operation_code)

GROUP BY date_slot, part_number, scrap_code, operation_code;

Thank you- Robert-

2

I'm not 100% sure on this but I think your problem is here

I think you should try something more like

JOIN table2
ON table1.part_number = table2.part_number and 
   fk_scrap_code = scrap_code and
   fk_part_number = part_number
3

You can now be 100% sure-
Your solution worked as expected-

Thank you for your time; it is very much appreciated-
Robert-

1 Like