Re: Aggregate functions on empty sets in SQL

In article <mHXF7.15042$xS6.22153_at_www.newsranger.com>, D_at_B.A says...
>>Every
>>aggregate function internally starts a loop with some value (for example, sum
>>starts with 0), and that value is the intuitive result of quering the empty set.
>
>Except avg.

Is avg a "true" aggregate function? I have several reasons why not:

1. It is easily expressed through sum:

select sum(col)/sum(1) from tbl;

2. Avg being just a weighted sum, there is nothing special about it, because the weight choice is completely arbitrary.

3. It doesn't obey the law:

select aggr(col) from (
select aggr(col) from t1
union all
select aggr(col) from t2
)

identical to

select aggr(col) from (
select col from t1
union
select col from t1
)

I would suggest this law as a definition of an aggregate function. Received on Wed Nov 07 2001 - 20:24:25 CET