LINQ with groupby and count

Question

This is pretty simple but I'm at a loss: Given this type of data set:

UserInfo(name, metric, day, other_metric)

and this sample data set:

joe  1 01/01/2011 5
jane 0 01/02/2011 9
john 2 01/03/2011 0
jim  3 01/04/2011 1
jean 1 01/05/2011 3
jill 2 01/06/2011 5
jeb  0 01/07/2011 3
jenn 0 01/08/2011 7

I'd like to retrieve a table that lists metrics in order(0,1,2,3..) with the total number of times the count occurs. So from this set, you'd end up with:

0 3    
1 2    
2 2    
3 1

I'm grappling with the LINQ syntax but am stuck on where to put a groupby and count... any help?

POST Edit: I was never able to get the posted answers to work as they always returned one record with the number of different counts. However, I was able to put together a LINQ to SQL example that did work:

var pl = from r in info
         orderby r.metric    
         group r by r.metric into grp
         select new { key = grp.Key, cnt = grp.Count()};

This result gave me an ordered set of records with 'metrics' and the number of users associated with each. I'm clearly new to LINQ in general and to my untrained eye this approach seems very similar to the pure LINQ approach yet gave me a different answer.

Answer 1

After calling GroupBy, you get a series of groups IEnumerable<Grouping>, where each Grouping itself exposes the Key used to create the group and also is an IEnumerable<T> of whatever items are in your original data set. You just have to call Count() on that Grouping to get the subtotal.

foreach(var line in data.GroupBy(info => info.metric)
                        .Select(group => new { 
                             Metric = group.Key, 
                             Count = group.Count() 
                        })
                        .OrderBy(x => x.Metric))
{
     Console.WriteLine("{0} {1}", line.Metric, line.Count);
}

---

> This was a brilliantly quick reply but I'm having a bit of an issue with the first line, specifically "data.groupby(info=>info.metric)"

I'm assuming you already have a list/array of some class that looks like

class UserInfo {
    string name;
    int metric;
    ..etc..
} 
...
List<UserInfo> data = ..... ;

When you do data.GroupBy(x => x.metric), it means "for each element x in the IEnumerable defined by data, calculate it's .metric, then group all the elements with the same metric into a Grouping and return an IEnumerable of all the resulting groups. Given your example data set of

    <DATA>           | Grouping Key (x=>x.metric) |
joe  1 01/01/2011 5  | 1
jane 0 01/02/2011 9  | 0
john 2 01/03/2011 0  | 2
jim  3 01/04/2011 1  | 3
jean 1 01/05/2011 3  | 1
jill 2 01/06/2011 5  | 2
jeb  0 01/07/2011 3  | 0
jenn 0 01/08/2011 7  | 0

it would result in the following result after the groupby:

(Group 1): [joe  1 01/01/2011 5, jean 1 01/05/2011 3]
(Group 0): [jane 0 01/02/2011 9, jeb  0 01/07/2011 3, jenn 0 01/08/2011 7]
(Group 2): [john 2 01/03/2011 0, jill 2 01/06/2011 5]
(Group 3): [jim  3 01/04/2011 1]

Answer 2

Assuming userInfoList is a List<UserInfo>:

var groups = userInfoList.GroupBy(n => n.metric)
                         .Select(n => new
                          {
                               MetricName = n.Key,
                               MetricCount = n.Count()
                          })
                         .OrderBy(n => n.MetricName);

The lambda function for GroupBy(), n => n.metric means that it will get field metric from every UserInfo object encountered. The type of n is depending on the context, in the first occurrence its of type UserInfo, because the list contains UserInfo objects. In the second occurrence n is of type Grouping, because now its a list of Grouping objects.

Groupings have extension methods like .Count(), .Key() and pretty much anything else you would expect. Just as you would check .Length on a string, you can check .Count() on a group.

Answer 3

userInfos.GroupBy(userInfo => userInfo.metric)
        .OrderBy(group => group.Key)
        .Select(group => Tuple.Create(group.Key, group.Count()));