1

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
AnalystCave.com
  • 4,884
  • 2
  • 22
  • 30

1 Answers1

2

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) )
DRapp
  • 47,638
  • 12
  • 72
  • 142