title: Case and Count in SQL Server 2008
body: <p>I have a table that stores multiple items for a state and I want to get count for every states according to specific conditions. I wrote this query:</p>
  
  <pre><code>SELECT
      State_ID,
      State_Name,
      State_All= CASE WHEN type1=1 AND type2=1 THEN COUNT(Id) END
      State_w= CASE WHEN type1=2 AND type2=1 THEN COUNT(Id) END
      State_s= CASE WHEN type1=2 AND type2=2 THEN COUNT(Id) END
  FROM
      tblStates
  </code></pre>
  
  <p>but I get this Error:</p>
  
  <pre><code>Column 'State_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
  </code></pre>
  
  <p>When I added <code>GROUP BY</code> clause For State_ID,I got above error again for State_Name,and when added State_Name to <code>GROUP BY</code> clause ,I got error for State_All,State_w,State_s.</p>
  
  <p>I don't have a column called State_All,State_w,State_s in my table.</p>
  
  <p>How I can get count according to specific conditions without using <code>CURSORS</code>?</p>