>>
>>SELECT CODE, ;
>> count(iif(status,1,0)) as in_cnt, ;
>> count(iif(status,0,1)) as out_cnt, ;
>> sum(iif(status,0,amount_in)) as in_sum, ;
>> sum(iif(status,amount_out,0)) as out_sum ;
>> FROM {yourtable} ;
>> ORDER BY 1
>> GROUP BY 1
>>
>>Hopefully this'll do the trick for you.
>>
>>/Paul
>
>The select statement calculates the sum values corretly but does not calculate the count values correctly. It gives me the total number for all records for both values. It's as if it ignores the iif() within the count clause.

Chris,

It is ignoring the IIF() statement. The COUNT() function returns the number of fields that satisfy the GROUP BY expression and are, therefore, unaffected by the IIF(). However, if you change the COUNT() function to SUM() with the same expression, you should get the results you're after.

HTH,

George