>>>Hi,
>>>
>>>When I do the following SQL Select with SQL Server I get the expected results:
>>>

>>>SELECT employee, COUNT( case when type= '0' then 1 else null end ) as COUNT_0, COUNT( case when type= '1' then 1 else null end ) as 
>>>COUNT_1 FROM MyTable GROUP BY employee
>>>

>>>
>>>But when I do the following in VFP, I don't get the expected results:
>>>

>>>SELECT employee, COUNT( IIF( type= '0', 1, 0 ) ) as COUNT_0, COUNT( IIF( type = '1',1,0)) as COUNT_1 FROM MyTable WHERE 
>>>GROUP BY employee
>>>

>>>
>>>In VFP expression all records are counted. How do I make VFP SQL Select work as the SQL Server?
>>
>>That is because you didn't use the same as in SQL Server:
>>

>>COUNT( IIF( type= '0', 1, NULL ))
>>

>>NULL not zero :-)
>>
>>But I prefer SUM anyway (don't know why, have no logical explanations :-))
>>

>>SUM(IIF( type= '0', 1, 0))
>>

>
>I did try replacing 0 with NULL but I was getting an error. But your approach with SUM() should work. Thank you very much.


For me this is a BUG, you should case the result:

CAST(COUNT(IIF( type= '0',1,NULL)) as int) as COUNT_0