linq, group by and count

Question

I have a list of Unions with several members and another table with page hits by Union. Need a report that list each union, the number of each type of member and the clicks (page views).

clicks are on a separate table with a date and unionID

this is close but doesn't really have the results I want. The count shows the total of members but not the total of clicks. How do i get the total clicks? (count records in clicks table that match the UnionID)

(from c in db.view_Members_Details
 join h in db.tbl_Clicks on c.unionID equals h.unionID 
 group c by new { c.UnionName, h.unionID } into g
 select new
 {
     TotalClicks = g.Count(),
     UnionName = g.Key.UnionName,
     userTypeID1 = g.Where(x => x.UnionName.Equals(g.Key.UnionName) && x.userTypeID.Equals(1)).Count(),
     userTypeID2= g.Where(x => x.UnionName.Equals(g.Key.UnionName) && x.userTypeID.Equals(2)).Count(),
     userTypeID3= g.Where(x => x.UnionName.Equals(g.Key.UnionName) && x.userTypeID.Equals(3)).Count(),
 }).ToList();

results should be:

Clicks Count |  Union Name | userTypeID1 Count  | userTypeID2 Count  | userTypeID3 Count |

score 0 · Answer 1 · answered Sep 09 '11 at 20:14

0

I don't think you need the first condition in your WHERE, because you're already grouping on the UnionName.

g.Count(x => x.userTypeID == 3) //etc for 1, 2, 3

As for the clicks, try the following:

TotalClicks = g.Count(x => x.unionID == g.Key.unionID)

answered Sep 09 '11 at 20:14

James Johnson

45,496
8
73
110

thanks, removing the 'where' works good but the TotalClicks doesn't work. It just adds up all the records so it count the number of member and number of click all together. I would need them separate – Hector Sep 12 '11 at 14:46

linq, group by and count

1 Answers1