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