Re: Examples of SQL anomalies?

"Brian Selzer" <brian_at_selzer-software.com> wrote in message news:nFD9k.5753$LG4.2422_at_nlpi065.nbdc.sbc.com...
>
> "-CELKO-" <jcelko212_at_earthlink.net> wrote in message
> news:f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d_at_z66g2000hsc.googlegroups.com...
> >>> 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,
that
> >>> 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
seems
> 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