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:

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.

Yes I have, but jimmy's explanation helped me more so. However I was never able to get his example to work but it did lead me in a new direction. — Gio
@Jimmy used the functional syntax for LINQ expressions rather than standard LINQ query syntax, plus he decided to show the immediate execution of those functions rather than the delayed execution format. For a new person that would be confusing. Don't know why he did that. — Richard Robertson

Jimmy Jimmy · Accepted Answer · 2011-09-02T15:31:00

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);
}

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]

LINQ with groupby and count

3 Answers