>>Hi,
>>
>>If I select records that don't satisfy my criteria I get 0 for count(*), but NULL for sum(IIF(myCondition, 1,0))
>>
>>Is this a documented beahvior?
>>
>>Thanks.
>
>Hi William,
>
>I don't think this behavior is properly documented in Help. All I can find is
>-------------------------------------------------------------------------------------------------------------
>When you use aggregate functions, such as MAX( ), in the SQL SELECT command without a GROUP BY clause, and the system does not find any matching records, Visual FoxPro returns a result set with a single record and it sets _TALLY to 1. In versions before Visual FoxPro 9.0, the system does not return any records and it sets _TALLY to 0.
>--------------------------------------------------------------------------------------------
>
>It doesn't tell you that COUNT may return a number and the SUM would return NULL. So, I guess, you can find this by trial and error method.

Basically, yes. But it makes sense - count(*) will return the count of _any_ records, therefore no nulls because it is counting real records, and "no records found" returns a real zero, not a null, because it can't say "I don't know whether I found any records".

SUM(), however, has to account for the case when all the fields it sums contain null - in which case, the total will be equally absent, i.e. sum of no-not-null numbers is a null (how's "no-not-null" for a triple negation, eh?).

OK, one more try: if sum(xField) is sum of all nulls, what else can it be but null? Can't invent numbers when there aren't any.