jsmith jsmith - 1 year ago 150
C# Question

Calculating Weighted Average with LINQ

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

Example Data:



0200 0


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 Source

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;
        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.


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download