Re: Aggregates on empty result sets.

From: Vadim Tropashko <vadimtro_at_yho.cm>
Date: Tue, 27 Jul 2004 18:02:37 -0700
Message-ID: <27DNc.33$5c3.88_at_news.oracle.com>

Mikito Harakiri wrote:
> nzanella_at_cs.mun.ca (Neil Zanella) wrote in message news:<b68d2f19.0407271100.4e5fc0e4_at_posting.google.com>...
>

>>Hello,
>>
>>I wonder whether anyone knows what the SQL standard has to say about what
>>aggregate functions such as MAX() and SUM() should return when the result
>>set is empty. I also wonder whether poular database management systems
>>such as Oracle, PostgreSQL, MySQL, and SQLite all conform to the
>>standard with regards to this particular issue. I am particularly
>>interested in what the result should be when the underlying data
>>type for the fields of interest is INTEGER.

>
>
> SUM should return 0
> MIN should return the maximum element in the domain
> MAX should return the minimum element in the domain
>
> This is what makes sence mathematically. ANSII/ISO perspective is different.

I just doublechecked that CAS -- Mapple, for example, -- indeed knows these trivialities

 > sum('a[k]','k'=1..0);

                                   0

 > min();

                                infinity

Avg should be undefined of course. And the least ad-hock way to report this fact to user is raising exception

 > sum('a[k]','k'=1..0)/sum('1','k'=1..0); Error, numeric exception: division by zero

(as opposed to returning lame NULL).

                                   1

Received on Wed Jul 28 2004 - 03:02:37 CEST