title: linq, group by and count
body: <p>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).</p>
  
  <p>clicks are on a separate table with a date and unionID</p>
  
  <p>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)</p>
  
  <pre><code>(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 =&gt; x.UnionName.Equals(g.Key.UnionName) &amp;&amp; x.userTypeID.Equals(1)).Count(),
       userTypeID2= g.Where(x =&gt; x.UnionName.Equals(g.Key.UnionName) &amp;&amp; x.userTypeID.Equals(2)).Count(),
       userTypeID3= g.Where(x =&gt; x.UnionName.Equals(g.Key.UnionName) &amp;&amp; x.userTypeID.Equals(3)).Count(),
   }).ToList();
  </code></pre>
  
  <p>results should be:</p>
  
  <pre><code>Clicks Count |  Union Name | userTypeID1 Count  | userTypeID2 Count  | userTypeID3 Count |
  </code></pre>