title: Datatable Linq GroupBy, Aggregate Count
body: <p>I have a datatable that contains data below. i want it to group by code, name, region and month 
  then get the count of each month where covcode = (ip or OP) using LINQ.</p>
  
  <pre><code>Code    Name                Region       Month  CovCode
  6018    Provider - ONE      REGION2        1    OP
  6018    Provider - ONE      REGION2        1    IP
  6019    Provider - TWO      REGION3        2    OP
  6019    Provider - TWO      REGION3        2    IP
  6020    Provider - THREE    REGION4        3    IP
  6020    Provider - THREE    REGION4        3    IP
  6020    Provider - THREE    REGION4        3    OP
  </code></pre>
  
  <p>The result i want should be like this:</p>
  
  <pre><code>Code    Name                Region      MONTH1  IP  OP  MONTH2  IP  OP  MONTH3  IP  OP
  6018    Provider - ONE      REGION2        2    1   1      0    0   0      0    0   0
  6019    Provider - TWO      REGION3        0    0   0      2    1   1      0    0   0
  6020    Provider - THREE    REGION4        0    0   0      0    0   0      3    2   1
  </code></pre>
  
  <p>This is the linq that i used that returns an incorrect results. </p>
  
  <pre><code>var hospital = 
      from hosp in tblClaimsMain.AsEnumerable()          
      group hosp by hosp["ProviderCode"] into grp
      select new
      {
            ProviderCode = grp.Select(g =&gt; g["CODE"].ToString()).FirstOrDefault(),
        ProviderName = grp.Select(g =&gt; g["NAME"].ToString()).FirstOrDefault(),
        Region = grp.Select(g =&gt; g["REGION"].ToString()).FirstOrDefault(),
            MONTH1 = grp.Select(g =&gt; g["MONTH"].ToString() == "1").Count(),
            MONTH2 = grp.Select(g =&gt; g["MONTH"].ToString() == "2").Count(),
            MONTH3 = grp.Select(g =&gt; g["MONTH"].ToString() == "3").Count()
  
      };
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Hi, I now able to get the count per month, on the other hand, i still have problems getting the count per month per covcode. i used the linq below.</p>
  
  <pre><code>var hospital = 
  from hosp in tblClaimsMain.AsEnumerable()          
  group hosp by new{CODE=hosp["CODE"],NAME=hosp["NAME"],REGION=hosp["REGION"]} into grp
  select new
  {
        ProviderCode = grp.Key.CODE,
        ProviderName = grp.Key.NAME,
        Region = grp.Key.REGION,
        MONTH1 = grp.Count(g =&gt; g["MONTH"].ToString() == "1"),
        IP1 = grp.Count(g =&gt; g["COVCODE"].ToString() == "INPATIENT"),
        OP1 = grp.Count(g =&gt; g["COVCODE"].ToString() != "INPATIENT"),
        MONTH2 = grp.Count(g =&gt; g["MONTH"].ToString() == "2"),
        IP2 = grp.Count(g =&gt; g["COVCODE"].ToString() == "INPATIENT"),
        OP2 = grp.Count(g =&gt; g["COVCODE"].ToString() != "INPATIENT"),
        MONTH3 = grp.Count(g =&gt; g["MONTH"].ToString() == "3")
        IP3 = grp.Count(g =&gt; g["COVCODE"].ToString() == "INPATIENT"),
        OP3 = grp.Count(g =&gt; g["COVCODE"].ToString() != "INPATIENT"),
  };
  </code></pre>