Using linq to retrieve average score

Question

was hoping for a little help.

I am retrieving the following list from a database.

Name  AssessNumber Score
John     1            90
John     2            88
John     3            67
Steve    1            98
Steve    2            90

what I want is to group by average scores and display highest asessment number(int)

Name   AssessNumber   Score
John   3              81.6
Steve  2              94

The data will be stored in a List<lbResults>lb = new List<lbResults>()

How can I achieve this?

kind Regards

Should be doable with a straighforward `group by`. What have you tried? Where are you stuck? — Heinzi, Apr 14 '15 at 14:53
seems like `group by average score` will produce a lot of 1-item groups — Ňɏssa Pøngjǣrdenlarp, Apr 14 '15 at 14:56

haim770 · Accepted Answer · 2015-04-14T15:04:47.580

Try this:

var results = data.GroupBy(x => x.Name).Select(x => new
{
    Name = x.Key,
    AssessNumber = x.Max(z => z.AssessNumber),
    Score = x.Average(z => z.Score)
})

You can also return the original data structure (which you named lbResults):

var results = data.GroupBy(x => x.Name).Select(x => new lbResults
{
    Name = x.Key,
    AssessNumber = x.Max(z => z.AssessNumber),
    Score = x.Average(z => z.Score)
})

See Fiddle

Rahul Singh · Answer 2 · 2015-04-14T17:46:57.243

Try this:-

List<lblResults> result = data.GroupBy(x => x.Name)
                              .Select(x => new lbResults 
                                          { 
                                             Name = x.Key, 
                                             AssessNumber = x.Max(z => z.AssesName), 
                                             Score = x.Average(s => s.Score) 
                                          }
                                       ).ToList();

score 2 · Answer 3 · answered Apr 14 '15 at 15:00

I want is to group by average scores

From the output it looks like you want to group by Name and get the Max AssessNumber and average Score. Your query should be:

var query = lb.GroupBy(r => r.Name)
              .Select(grp => new 
                  {
                     Name = grp.Key, 
                     AccessNumber = grp.Max(i=> i.AccessNumber), 
                     AverageScore = grp.Avg(i => i.Score),
                  });

score 2 · Answer 4 · answered Apr 14 '15 at 15:01

Given:

public class lbResults
{
    public string Name { get; set; }
    public int Assess { get; set; }
    public int Score { get; set; }
}

you want:

 List<lbResults> lb = new List<lbResults>
 {
      new lbResults{ Name="John", Assess = 1, Score=90},
      new lbResults{ Name="John", Assess = 2, Score=88},
      new lbResults{ Name="John", Assess = 3, Score=67},
      new lbResults{ Name="Steve", Assess = 1, Score=98},
      new lbResults{ Name="Steve", Assess = 2, Score=90},
 };

var results = lb.GroupBy(l => l.Name)
                .Select(g => new 
                        { 
                          Name = g.Key, 
                          Assess = g.Max(gr => gr.Assess), 
                          Score = g.Average(gr => gr.Score) 
                        });

I like this as an answer. Could you possibly talk through what's going on with the .Max(gr => gr.Assess) and the .Average(gr => gr.Score). What is gr short for? — JARRRRG, Apr 14 '15 at 15:51
`gr` is an arbitrary name i gave to the parameter required for `Max` and `Average`. Same as the arbitrary names `l` and `g` i used. — Jonesopolis, Apr 14 '15 at 15:54

score 0 · Answer 5 · answered Jun 15 '16 at 22:32

Lets have players:

var players = new List<Player> {
    new Player { Name = "John",  AssessNumber = 1, Score = 90 },
    new Player { Name = "John",  AssessNumber = 2, Score = 88 },
    new Player { Name = "John",  AssessNumber = 3, Score = 67 },
    new Player { Name = "Steve", AssessNumber = 1, Score = 98 },
    new Player { Name = "Steve", AssessNumber = 2, Score = 90 },
};

This code shows grouping using linq query expression syntax:

var playerInfos =
    from player in players
    group player by player.Name into playerGroup
    select new
    {
        PlayerName = playerGroup.Key,
        MaxAssessNumber = playerGroup.Max(x => x.AssessNumber),
        AverageScore = playerGroup.Average(x => x.Score),
    };

The result playerInfos is collection of anonymous objects:

{ PlayerName = "John",  MaxAssessNumber = 3, AverageScore = 81.66666... }
{ PlayerName = "Steve", MaxAssessNumber = 2, AverageScore = 94.0 }

See more info about LINQ Average aggregate method.

Using linq to retrieve average score

5 Answers5