Problem
This is quite straightforward, yet I’m stumped: Given this type of data, consider the following:
UserInfo(name, metric, day, other_metric)
as well as the following 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 want to get a table with metrics listed in ascending order (0, 1, 2, 3, etc.) and the total number of times the count happens. As a result of this set, you’d get:
0 3
1 2
2 2
3 1
I’m learning LINQ but can’t figure out where to place a groupby and count… any suggestions?
POST Edit: I could never get the posted answers to work because they always returned a single record with the amount of different counts. However, I was able to put together a working LINQ to SQL example:
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 provided me an ordered list of records with’metrics’ and the number of users for each. I’m new to LINQ in general, and this approach looks very similar to the pure LINQ approach to my uneducated eye, however it gave me a different answer.
Asked by Gio
Solution #1
You obtain a sequence of IEnumerableGrouping> after executing GroupBy, where each Grouping exposes the Key used to construct the group and is also an IEnumerableT> of whatever items are in the original data set. To retrieve the subtotal, simply call Count() on that Grouping.
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 it comes to data. “For each element x in the IEnumerable provided by data, compute its.metric, then group all the elements with the same metric into a Grouping and return an IEnumerable of all the resulting groups,” according to GroupBy(x => x.metric). Given the data set you’ve provided as an example,
<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
Following the groupby, the following result would be obtained:
(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]
Answered by Jimmy
Solution #2
If userInfoList is a ListUserInfo>, then:
var groups = userInfoList.GroupBy(n => n.metric)
.Select(n => new
{
MetricName = n.Key,
MetricCount = n.Count()
})
.OrderBy(n => n.MetricName);
The n => n.metric lambda method for GroupBy() means that it will get field metric from each UserInfo object encountered. Because the list comprises UserInfo objects, the type of n varies depending on the context. In the first instance, it is of type UserInfo. Because it’s now a list of Grouping objects, n is of type Grouping in the second occurrence.
Extension methods for groupings include.Count(),.Key(), and pretty much anything else you’d expect. Just like you’d do if you were checking. You can check the length of a string. On a group, use count().
Answered by Vladislav Zorov
Solution #3
userInfos.GroupBy(userInfo => userInfo.metric)
.OrderBy(group => group.Key)
.Select(group => Tuple.Create(group.Key, group.Count()));
Answered by DanielOfTaebl
Post is based on https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count