Excel ACE.OLEDB: COUNTIF equivalent in query

Question

I developed the following Microsoft Query in ACE.OLEDB:

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

The query works fine but it lasts forever to execute on over 90k records in Excel.

Is it possible to optimize this query by using a COUNTIF equivalent?

I image the query could be optimized if it worked like this:

SELECT 
    Name, Country, 
    COUNTIF(CASE WHEN Description="Work" THEN 1 ELSE 0 END) / COUNT(*)
FROM 
    Table1 as T
GROUP BY 
    Name, Country

In reply to suggestions to you the CASE WHEN, I tried a simple proof of concept query:

SELECT SUM(CASE WHEN Description="Work" THEN 1 ELSE 0 END) 
FROM (SELECT "Work" as Description)

and I get an Unrecognized keyword WHEN error.

Answer 1

Count will just count things. You should do a SUM

SUM(CASE WHEN Description="Work" THEN 1 ELSE 0 END) 

If it is not work, it sums a 0, otherwise a 1.

Looking more at your tags, you mention Excel. You might need to change it to

SUM( IIF( Description="Work", 1, 0) )