Re: Examples of SQL anomalies?
Date: Thu, 03 Jul 2008 13:24:44 GMT
"Brian Selzer" <brian_at_selzer-software.com> wrote in message
> "-CELKO-" <jcelko212_at_earthlink.net> wrote in message
> >>> The question is, if these issues are due to the SQL specification or
> >>> simply due to a problem in a specific SQL product. Or could it be,
> >>> the definition is not precise enough in some points, so that database
> >>> vendors implemented it differently? <<
> > Nope, it is the specs. All aggregate (set) functions begin by
> > removing the NULLs from their parameter set, then if there is a
> > DISTINCT option on the parameter, they remove redundant duplicates and
> > finally do the operation (MIN, MAX, AVG, SUM, COUNT on what is left.
> > Since an empty set has no elements upon which to apply an operation,
> > SQL returns a NULL (okay, it should be an "undefined" if we were
> > mathematically correct).
> MIN, MAX and AVG are meaningless when applied to an empty bag, but it
> to me that COUNT should always return 0 when the bag is empty, and
> similarly, SUM should return 0. SUM should only return NULL if one of the
> values to be summed is NULL.
By definition, none of the values to be summed are NULL.
I agree about COUNT and SUM on an empty bag. BTW, the DISTINCT keyword can
be used with aggregates to obtain a projection: select count (distinct
zip_code) from employees .
Received on Thu Jul 03 2008 - 15:24:44 CEST
I agree about COUNT and SUM on an empty bag. BTW, the DISTINCT keyword can be used with aggregates to obtain a projection: select count (distinct zip_code) from employees . Received on Thu Jul 03 2008 - 15:24:44 CEST