Duplicate sum values in a one-to-many join

[#1]

Hello

I have two files, the first with unique claim number (one record per claim) and cost, and the second with non-unique claim numbers (possibly many records per claim) and a code. When I load them in to QV and sum the cost by claim number, the values are multiplied up by the number of duplicates on the second file. For example:

status:
load * inline [
claim, cost
A, 100
B, 150
C, 120
D, 90
];

status2:
LEFT JOIN load* inline [
claim, rule
A, 1
B, 1
B, 2
C, 1
D, 2
D, 3];

I this example if you create a table to sum cost by claim, claim B will show 300 and D 180.

How do I resolve this?

Many thanks!

[hector]

Hi, I answered this in other post. Maybe another solution is use Avg() instead Sum()

H.

[#3]

Hi,

if table1always has just one cost per claim you can use sum(distinct cost).