P.S.
From: Michael C# (xyz_at_abcdef.com)
Date: 02/27/05
- Next message: Norbert Meiss: "Re: Using master..xp_logininfo"
- Previous message: Michael C#: "Re: Can't login into SQL Server Database"
- In reply to: --CELKO--: "Re: Question about SUM and Nulls"
- Next in thread: Steve Kass: "Re: Question about SUM and Nulls"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 27 Feb 2005 14:55:30 -0500
Seeing as how I got your attention for a hot minute, I'd like to ask you
some questions about implementing your improved Soundex algorithm. Do you
have a few minutes to answer them?
Thx,
Mike C.
"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1109512331.792043.28870@z14g2000cwz.googlegroups.com...
>>> I'm no mathematician, but if SUM(100, NULL, 150) is defined
> differently from
> 100+NULL+150 mathematically, then I suppose there's no room to question
> the
> choices made. <<
>
> I am and it is! A summation is not the same as an addition.
> Summation works on sets, and those sets can be infinite. Addition is a
> binary operation that works on scalars. This was part of the big jump
> that Cantor made with set theory; the whole does not behave like the
> parts. Before then, we worked with series and sequences that were
> coutable and talked about limits.
>
>>> I'd like to hear a mathematician speak to whether this is a
> standard mathematical definition or if SUM() or if it was an arbitrary
> decision made by ANSI. <<
>
> We talked about this in the ANSI X3H2 committee. A lot of the behavior
> was justified after the fact, since we had existing products at the
> point that SQL-86 was written. The decison was that *all* aggreate
> functions begin by computing a group of values from the expression they
> are given, then remove NULLs from the group, if there is a DISTINCT
> option also remove redundant duplicates and finally perform the
> computation to reduce the group to a scalar value. It is not a matter
> of changing NULLs to 0 or 1; they are dropped.
>
> The reasoning was that this is the most useful way to compute an
> aggregate 98% of the time. For the other 2%, there is a SQLSTATE
> warning that tells you which groups have dropped NULLs so you can catch
> it in the program one group at a time. And we already mentioned using
> COALESCE() in the expression as a solution for some of these problem.
> And there is the "COUNT(*) <> COUNT(<key_col>)" test in a CASE
> expression.
>
> COUNT(*) is a very different animal. It is the cardinality of the
> whole set and not an aggregate of the data elements at all. Play with
> aggregates on a table with only NULLs and one that is empty. An empty
> set has cardinality zero, but returns an empty set of aggregations (ab
> null, ex nullo).
>
> The syntax probably should have been something like "CARD (<table
> expression>)" to match "[NOT] EXIST (<table expression>)" and then we
> would use "COUNT(<key col>)" instead of "COUNT(*)".
>
- Next message: Norbert Meiss: "Re: Using master..xp_logininfo"
- Previous message: Michael C#: "Re: Can't login into SQL Server Database"
- In reply to: --CELKO--: "Re: Question about SUM and Nulls"
- Next in thread: Steve Kass: "Re: Question about SUM and Nulls"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
