Is there a proper name for the type of Average which the AVG() function
returns, where NULLs are eliminated versus: an Average calculated by
dividing the SUM() of the column by the COUNT(*) of the table?
It seems as if the AVG() function by itself returns the same result as if
you assigned an 'average' value to the NULLs:
i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
It also seems as if dividing the SUM() of the column by the COUNT(*) of the
table gives the same result as if you treated NULLs as zero:
i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
return 10; SUM(column)/COUNT(*) would return 10 as well.
Anyway, just wondering if there was a proper name for these different
averages. Thanks.I do not think there is a proper name... The thing is that if you consider
NULLs you must give them some value, when you use sum/ Count(*) it act as
if the Null values are zero...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Michael C#" <xyz@.abcdef.com> wrote in message
news:2Bfte.15144$l_2.5675@.fe09.lga...
> Is there a proper name for the type of Average which the AVG() function
> returns, where NULLs are eliminated versus: an Average calculated by
> dividing the SUM() of the column by the COUNT(*) of the table?
> It seems as if the AVG() function by itself returns the same result as if
> you assigned an 'average' value to the NULLs:
> i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
> would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
> It also seems as if dividing the SUM() of the column by the COUNT(*) of
> the table gives the same result as if you treated NULLs as zero:
> i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
> would return 10; SUM(column)/COUNT(*) would return 10 as well.
> Anyway, just wondering if there was a proper name for these different
> averages. Thanks.
>|||On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>Is there a proper name for the type of Average which the AVG() function
>returns, where NULLs are eliminated versus: an Average calculated by
>dividing the SUM() of the column by the COUNT(*) of the table?
>It seems as if the AVG() function by itself returns the same result as if
>you assigned an 'average' value to the NULLs:
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
>It also seems as if dividing the SUM() of the column by the COUNT(*) of the
>table gives the same result as if you treated NULLs as zero:
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
>Anyway, just wondering if there was a proper name for these different
>averages. Thanks.
>
Hi Michael,
The proper name is "average".
>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
I'd call this "average" or, if I really must be explicit, "average of
only the values that are not missing" (but since eliminating NULLS
before applying an aggregate is SOP in SQL, that's really just some
extra unnecessary redundancy).
>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0)) would
>return 10; SUM(column)/COUNT(*) would return 10 as well.
I'd call this "average of the set after replacing missing values by the
arbitrarily chosen value of zero". Of course, the function itself is
still just called "average" - the rest actually described the COALESCE
expression that is used as argument for the AVG().
(Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The AVG function is simply the Arithmetic Mean but with the additional rule
that NULL values are ignored. If you prefer to be a bit more explicit about
that calculation then maybe you could add x IS NOT NULL to your WHERE
clause. If you want to include NULLs in the computation then you can
substitute some alternative expression inside the AVG funcion. For example:
AVG(COALESCE(x,0)).
--
David Portas
SQL Server MVP
--|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:jqnbb19pig8vfd8ev7pgtqj59gact2h2ou@.4ax.com...
> On Sun, 19 Jun 2005 10:48:23 -0400, Michael C# wrote:
>>Is there a proper name for the type of Average which the AVG() function
>>returns, where NULLs are eliminated versus: an Average calculated by
>>dividing the SUM() of the column by the COUNT(*) of the table?
>>It seems as if the AVG() function by itself returns the same result as if
>>you assigned an 'average' value to the NULLs:
>>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
>>It also seems as if dividing the SUM() of the column by the COUNT(*) of
>>the
>>table gives the same result as if you treated NULLs as zero:
>>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
>>would
>>return 10; SUM(column)/COUNT(*) would return 10 as well.
>>Anyway, just wondering if there was a proper name for these different
>>averages. Thanks.
> Hi Michael,
> The proper name is "average".
>>i.e., for the set { 5, 10, 15, NULL, 20 }, the AVG() average is 12.5; you
>>would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 20 }
> I'd call this "average" or, if I really must be explicit, "average of
> only the values that are not missing" (but since eliminating NULLS
> before applying an aggregate is SOP in SQL, that's really just some
> extra unnecessary redundancy).
>>i.e., for the set {5, 10, 15, NULL, 20 }, the AVG(COALESCE(column, 0))
>>would
>>return 10; SUM(column)/COUNT(*) would return 10 as well.
> I'd call this "average of the set after replacing missing values by the
> arbitrarily chosen value of zero". Of course, the function itself is
> still just called "average" - the rest actually described the COALESCE
> expression that is used as argument for the AVG().
> (Similarly, for AVG(A + B), I'd say "the average of the sum of A and B")
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
It's all redundancy; that's why I was wondering if there was a more
'concise' way to say it.
I guess "10 words or less" is out of the question.|||Actually I was just wondering if there was a more concise way to say it.
Apparently "SQL AVG()" is about as concise as it gets; it seems there's no
concise term to describe how it arrives at an answer.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:hL2dna5IZ4cocyjfRVn-2w@.giganews.com...
> The AVG function is simply the Arithmetic Mean but with the additional
> rule that NULL values are ignored. If you prefer to be a bit more explicit
> about that calculation then maybe you could add x IS NOT NULL to your
> WHERE clause. If you want to include NULLs in the computation then you can
> substitute some alternative expression inside the AVG funcion. For
> example: AVG(COALESCE(x,0)).
> --
> David Portas
> SQL Server MVP
> --
>
