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.
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) )