jsmith - 4 months ago 49

C# Question

My goal is to get a weighted average from one table, based on another tables primary key.

Example Data:

Table1

`Key WEIGHTED_AVERAGE`

0200 0

Table2

`ForeignKey Length Value`

0200 105 52

0200 105 60

0200 105 54

0200 105 -1

0200 47 55

I need to get a weighted average based on the length of a segment and I need to ignore values of -1. I know how to do this in SQL, but my goal is to do this in LINQ. It looks something like this in SQL:

`SELECT Sum(t2.Value*t2.Length)/Sum(t2.Length) AS WEIGHTED_AVERAGE`

FROM Table1 t1, Table2 t2

WHERE t2.Value <> -1

AND t2.ForeignKey = t1.Key;

I am still pretty new to LINQ, and having a hard time figuring out how I would translate this. The result weighted average should come out to roughly 55.3. Thank you.

Answer

I do this enough that I created an extension method for LINQ.

```
public static double WeightedAverage<T>(this IEnumerable<T> records, Func<T, double> value, Func<T, double> weight)
{
double weightedValueSum = records.Sum(x => value(x) * weight(x));
double weightSum = records.Sum(x => weight(x));
if (weightSum != 0)
return weightedValueSum / weightSum;
else
throw new DivideByZeroException("Your message here");
}
```

After you get your subset of data the call looks like this.

```
double weightedAverage = records.WeightedAverage(x => x.Value, x => x.Length);
```

This has become extremely handy because I can get a weighted average of any group of data based on another field within the same record.

**Update**

I now check for dividing by zero and throw a more detailed exception instead of returning 0. Allows user to catch the exception and handle as needed.