title: Linq query with multiple count
body: <p>I am trying to query this table using LINQ:</p>
  
  <p><img src="https://i.stack.imgur.com/Y2pV7.png" alt="enter image description here"></p>
  
  <p>Here's what I want to do:</p>
  
  <p><img src="https://i.stack.imgur.com/c89I2.png" alt="enter image description here"></p>
  
  <p>Here's my LINQ query:</p>
  
  <pre><code>var query = from a in table 
              where a.Country.Equals("USA")
              group a by a.Product_brand into grp
              select new
              {
                Product_brand = grp.key.Product_brand,
                Country = grp.Key.Country,
                Black = grp.Count(a =&gt; a.Black=="Yes"),
                White = grp.Count(a =&gt; a.White=="Yes"),
                Red = grp.Count(a=&gt; a.Red=="Yes"),
                Green = grp.Count(a=&gt; a.Green=="Yes")
              }
  </code></pre>
  
  <p>I don't know what's wrong with my query, I keep getting this message:</p>
  
  <p><img src="https://i.stack.imgur.com/dQDhT.png" alt="enter image description here"></p>
  
  <p>Alternative Solution:</p>
  
  <p>Sql query:</p>
  
  <pre><code>SELECT [Product brand], Country,
  sum(case when [Black] = 'Yes' then 1 else 0 end) as Black,
  sum(case when [White] = 'Yes' then 1 else 0 end) as White,
  sum(case when [Red] = 'Yes' then 1 else 0 end) as Red,
  sum(case when [Green] = 'Yes' then 1 else 0 end) as Green,
  FROM            dbo.Table
  
  group by [Product brand], Country
  </code></pre>