I can see that sometimes you might want it to behave like this. But
there would be plenty of simple methods to achieve the same without
Sum returning Null. On the other hand, not being able to embed a sum
query into anything, and not being able to include the result in a
calculation, is a serious limitation, and the alternatives can be
very long winded. Often you have to write a procedure to return a
result set, when a single query would do if sum was useable.

It really makes very little difference whether I am criticising Ib,
or Firebird, or the SQL standard. The actual behaviour is
unintuitive and illogical, and very incovenient.

--- In ib-support@y..., "jackfrosch" <jfrosch@f...> wrote:
> >> 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.
>
> However, consider an invoice system that mails account statements
> each month for any account with activity. Getting a zero return
> implies that the credits and debits cancelled each other out.
> Getting a NULL means there was no activity and no statement need be
> printed and mailed. If SUM returned zero in both cases, an
> evaluation of COUNT (or other indicator) would be necessary.
>
> Semantically zero and NULL are different. If one is asked, "How
many
> times have you been caught shoplifting?", simply answering "None"
is
> a bit ambiguous! <g>
>
> Just my two cents...
>
> Jack
>
> --- In ib-support@y..., "rogervellacott" <rvellacott@p...> wrote:
> > 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. If there are no records, then the total
is
> > zero. It is not "unknown".
> >
> > It means that an update query such as
> >
> > UPDATE TABLE_X SET FIELD_X = FIELD_X + (SELECT SUM( etc.....
> >
> > not only fails, but also loses the original value of FIELD_X.
> >
> > This seems very dangerous to me. Is it as intended? Does it
conform
> > to SQL standards? Can it be changed in a future release?