I run into aggregation problems when I have a table with detail that I need to sum to join with a table with summary information. Example is a table with vehicle repair orders by repair line. I join it with a labor table that has totals by repair line. No problems so far. Then I join with the parts table that has multiple rows (i.e. individual parts used on each line - many times more than one part per repair line) per repair line and I want to sum the price and cost of the parts by line.
I get double and triple the correct amounts when I do a SUM on those two columns from the Parts detail table (depending on - I think - the number of rows per repair line.
Will a subquery work for those two columns? I have attached the entire statement and you can see the two sum columns (the only two)
This is for an Iseries