title: Sql Server equivalent of a COUNTIF aggregate function
body: <p>I'm building a query with a <code>GROUP BY</code> clause that needs the ability to count records based only on a certain condition (e.g. count only records where a certain column value is equal to 1).</p>
  
  <pre><code>SELECT  UID, 
          COUNT(UID) AS TotalRecords, 
          SUM(ContractDollars) AS ContractDollars,
          (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1
  FROM    dbo.AD_CurrentView
  GROUP BY UID
  HAVING  SUM(ContractDollars) &gt;= 500000
  </code></pre>
  
  <p>The <code>COUNTIF()</code> line obviously fails since there is no native SQL function called <code>COUNTIF</code>, but the idea here is to determine the percentage of all rows that have the value '1' for MyColumn.</p>
  
  <p>Any thoughts on how to properly implement this in a MS SQL 2005 environment?</p>