crosstab query vs Sum(IIf([MyFiled]="D",1,0)) AS SumD

[inungh]

I just realized that I can translate crosstab query to Sum(iif(true,
1,0)) function.

Please let me know if I am wrong.

If it does, are there any benefits to use Sum iif function or crosstab
query for example for performance?


Your informaiton is great appreciated,

 

[Michel Walsh]

Among other differences:


-The iif formulation has to know, hard coded, the values to be tested before
running the query.



-The iif formulation repeats the test for each generated field :

SUM(iif(country="USA",1, 0)) as inUSA, SUM(iif(country="Canada", 1,
0) AS inCanada, SUM(iif(... )) ...


-The result MAY differ if a group does not have any data under a generated
column: the iif produces a 0, the crosstab produces a NULL (which can be
coalesce to 0).


-The iif formulation is much more verbose, so more susceptible to typo
errors, and probably harder to decipher.


-The iif formulation does not allow an aggregate over the created field,
while the crosstab Jet syntax does. An example is more illustrative:

TRANFORM COUNT(f1) AS theCell
SELECT ..., COUNT(theCell), ...
FROM ...
GROUP BY ...
PIVOT ...


where, here, COUNT(theCell) returns, for the actual group, the number of
generated fields for that specific group, as example.





Vanderghast, Access MVP