>> 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?