Re: Examples of SQL anomalies?

From: David Cressey <>
Date: Thu, 03 Jul 2008 13:24:44 GMT
Message-ID: <wq4bk.28$0V1.10_at_trndny01>

"Brian Selzer" <> wrote in message news:nFD9k.5753$
> "-CELKO-" <> 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

Original text of this message