""jackfrosch"" <jfrosch@...> wrote in message
news:a587id+h1il@......

> >> The query SELECT SUM(NUMERIC_FIELD_X) FROM TABLE_X WHERE ...
> returns NULL if no records are found. Both logic and intuition
> suggest that it should return zero. <<
>
> I'm not sure I agree about the logic of returning zero if no records
> are found, though in most cases no results would be synonymous with a
> zero sum.

I think the behavior is ANSI-SQL compliant.
SUM, MIN, MAX and AVG can return NULL if they don't find records or all
records they visit produce NULL in the field or expression those functions
use.

COUNT always returns a numeric result. There was a bug in IB6 that returned
zero when there were no records, even if COUNT was added to anything, but
Firebird fixed it and probably was fixed in IB, too. The bug was:

select count(*)+10 from empty_table;
=> 0 => should be 10.

Besides that, count(*) and count(field) are different, of course.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing