CODEWITHSUNDEEP
sqlms-accessaggregate-functionsiif
Jaanus
Jaanus

Reputation: 16541

Using IIF in Access give me aggregate function error

SELECT Bot.BetType, 
   Sum(Bot.Result) AS Won, 
   IIf([Bot]![Market Name] Like "*Place*", "Place", "Win") AS Type
FROM Bot
GROUP BY Bot.BetType, Type;

Getting error:

You tried to execute a query that does not include the specified expression If([Bot]![Market Name] Like "*Place*", "Place", "Win") as part of an aggregate function.

I did not find a result from google. If you have any questions, feel free to ask.

Upvotes: 1

Views: 3847

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If you use a subquery, I think you can use the alias:

SELECT B.BetType, 
       Sum(B.Result) AS Won,
       type
from (select b.*, IIf([Bot]![Market Name] Like "*Place*", "Place", "Win") AS Type
      FROM Bot b
     ) b
GROUP BY b.BetType, Type;

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

You cannot use aliases in GROUP BY:

SELECT Bot.BetType, 
   Sum(Bot.Result) AS Won, 
   IIf([Bot]![Market Name] Like "*Place*", "Place", "Win") AS Type
FROM Bot
GROUP BY Bot.BetType, IIf([Bot]![Market Name] Like "*Place*", "Place", "Win");

Upvotes: 3

Related Questions