title: Excel ACE.OLEDB: COUNTIF equivalent in query
body: <p>I developed the following Microsoft Query in ACE.OLEDB:</p>
  
  <pre><code>SELECT 
      Name, Country 
      (SELECT COUNT(*) 
       FROM Table1 as T1 
       WHERE Name = T.Name 
         AND Country = T.Country 
         AND Description="Work"
       GROUP BY Name, Country) / COUNT(*)
  FROM 
      Table1 as T
  GROUP BY 
      Name, Country
  </code></pre>
  
  <p>The query works fine but it lasts forever to execute on over 90k records in Excel. </p>
  
  <p><strong>Is it possible to optimize this query by using a <code>COUNTIF</code> equivalent?</strong> </p>
  
  <p>I image the query could be optimized if it worked like this:</p>
  
  <pre><code>SELECT 
      Name, Country, 
      COUNTIF(CASE WHEN Description="Work" THEN 1 ELSE 0 END) / COUNT(*)
  FROM 
      Table1 as T
  GROUP BY 
      Name, Country
  </code></pre>
  
  <p>In reply to suggestions to you the CASE WHEN, I tried a simple proof of concept query:</p>
  
  <pre><code>SELECT SUM(CASE WHEN Description="Work" THEN 1 ELSE 0 END) 
  FROM (SELECT "Work" as Description)
  </code></pre>
  
  <p>and I get an <code>Unrecognized keyword WHEN</code> error.</p>