Pros / Cons to this approach

I have a requirement where I need to perform a query for position
information. But for some types of entries, I need to "expand" the row
to include additional position rows. Let me explain with an example:

An index is a security that is made up of components where each
component has a "weight" or a number of shares. So if I have 1 share of
the index, I have X shares of each component.

AAPL is an Equity, CSCO is an Equity, SPY is an Index. Lets say that
SPY has one component, AAPL, with shares being 10. (1 share of SPY = 10
shares of AAPL).

So, I do some trading and I end up with positions as follows:

+10 AAPL
-5 CSCO
+2 SPY

The query I need returns:

+10 AAPL
-5 CSCO
+2 SPY
+20 AAPL (from 2 SPY * 10 shares)

which becomes (after grouping):

+30 AAPL
-5 CSCO
+2 SPY

-------------

Based on that criteria and the following schema (and sample data):

-- Drop tables
DROP TABLE [SecurityMaster]
DROP TABLE [Position]
DROP TABLE [IndexComponent]

-- Create tables
CREATE TABLE [SecurityMaster] (
[Symbol] VARCHAR(10)
, [SecurityType] VARCHAR(10)
)

CREATE TABLE [Position] (
[Account] VARCHAR(10)
, [Symbol] VARCHAR(10)
, [Position] INT
)

CREATE TABLE [IndexComponent] (
[IndexSymbol] VARCHAR(10)
, [ComponentSymbol] VARCHAR(10)
, [Shares] INT
)

--Populate tables
INSERT INTO [SecurityMaster] VALUES ('AAPL', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MSFT', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('MNTAM', 'Option')
INSERT INTO [SecurityMaster] VALUES ('CSCO', 'Equity')
INSERT INTO [SecurityMaster] VALUES ('SPY', 'Index')

INSERT INTO [Position] VALUES ('001', 'AAPL', 10)
INSERT INTO [Position] VALUES ('001', 'MSFT', -5)
INSERT INTO [Position] VALUES ('001', 'CSCO', 10)
INSERT INTO [Position] VALUES ('001', 'SPY', 15)
INSERT INTO [Position] VALUES ('001', 'QQQQ', 21)
INSERT INTO [Position] VALUES ('002', 'MNTAM', 10)
INSERT INTO [Position] VALUES ('002', 'APPL', 20)
INSERT INTO [Position] VALUES ('003', 'SPY', -2)

INSERT INTO [IndexComponent] VALUES ('SPY', 'AAPL', 25)
INSERT INTO [IndexComponent] VALUES ('SPY', 'CSCO', 50)
INSERT INTO [IndexComponent] VALUES ('QQQQ', 'AAPL', 33)

-- *****************************

-- Based on the rules:
-- 1) Index positions appear like other positions (account /
symbol) pair, but
-- its components show up as new rows of account (of index),
symbol (equal
--to component symbol), position (equal to shares * index position)
-- 2) One row for each account / symbol pair (GROUP BY account and
symbol, SUM position)

-- Expected output (without grouping) (sorted by account / symbol)
-- 001 AAPL 10
-- 001 AAPL 375 (component shares * index position) (25
* 15) (SPY)
-- 001 AAPL 693 (component shares * index position) (33
* 21) (QQQQ)
-- 001 CSCO 10
-- 001 CSCO 750 (component shares * index position) (50
* 15) (SPY)
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50 (component shares * index position) (25
* -2) (SPY)
-- 003 CSCO -100 (component shares * index position) (50
* -2) (SPY)
-- 003 SPY -2

-- Expected output (with grouping account / symbol) (sorted by account
/ symbol)
-- 001 AAPL 1078
-- 001 CSCO 760
-- 001 MSFT -5
-- 001 QQQQ 21
-- 001 SPY 15

-- 002 AAPL 20
-- 002 MNTAM 10

-- 003 AAPL -50
-- 003 CSCO -100
-- 003 SPY -2

--------------

Is a UNION the best way to perform the query. What are the pros and
cons? What, if any, is a better way?

SELECT
[Account], [Symbol], SUM([Position]) AS [Position]
FROM
(
SELECT[Account], [Symbol] , [Position]
FROM[Position]

UNION ALL

SELECTP.[Account] , IC.[ComponentSymbol] AS [Symbol] , (P.[Position] *
IC.[Shares]) AS [Position]
FROM[IndexComponent] IC
JOIN[Position] P
ONP.[Symbol] = IC.[IndexSymbol]
) D
GROUP BY[Account], [Symbol]
ORDER BY[Account], [Symbol](JayCallas@.hotmail.com) writes:
> I have a requirement where I need to perform a query for position
> information. But for some types of entries, I need to "expand" the row
> to include additional position rows. Let me explain with an example:
> An index is a security that is made up of components where each
> component has a "weight" or a number of shares. So if I have 1 share of
> the index, I have X shares of each component.

Now, this sounds funny to me, because in our system you can also define
indexes. However, indexes are virtual - you can never have a position
in an index directly. (But you can have a position in a derivative that
has the index as its contract base.)

> Is a UNION the best way to perform the query. What are the pros and
> cons? What, if any, is a better way?

There might be other alternatives, but I think the UNION query is fine.
Since I was given this query in my lap, I ran out of fantasy of trying
something else.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Not all index can have positions. For instance, the Dow Jones
Industrial Average is an an index but you cannot buy shares of it, only
shares of the components. But there is a class of indices called ETF
(Exchange Traded Fund) that you can buy and sell shares of. Here is a
link for the definition of an ETF,
http://www.investorwords.com/1755/ETF.html. (There are probably better
ones out there but this gives the basics.)|||(JayCallas@.hotmail.com) writes:
> Not all index can have positions. For instance, the Dow Jones
> Industrial Average is an an index but you cannot buy shares of it, only
> shares of the components. But there is a class of indices called ETF
> (Exchange Traded Fund) that you can buy and sell shares of. Here is a
> link for the definition of an ETF,
> http://www.investorwords.com/1755/ETF.html. (There are probably better
> ones out there but this gives the basics.)

I think we have those in Sweden as well. I would guess that our
customers handle them as stocks. At least I have not heard of any
requirement to add any support for them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I expect the DOW JONES Industrial Average Index to rocket
up past 12500 by early 2006. Today it closed at 10530.

I won't be surprised if there is a Santa Claus Rally this year (2005).

Good luck,
Steve|||Stocks rally up early this coming week (Nov 7, 2005)

DOW +100 >
NAS +35 >
watch it happen
Good luck,
Steve

Proper name for average types

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
> --
>

Proper name for average types

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; y
ou
would get the same result for the AVG() of the set { 5, 10, 15, 12.5, 2
0 }
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 }[/vb
col]
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).
[vbcol=seagreen]
>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:
>
> Hi Michael,
> The proper name is "average".
>
> 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'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
> --
>

Proper name for average types

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:
>
> Hi Michael,
> The proper name is "average".
>
> 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'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
> --
>