Re: Semantics of SUM

On 14/11/2009 06:05, Steve Harris wrote:
> On 13 Nov 2009, at 17:05, Andy Seaborne wrote:
>> Summary :
>> SQL standard requires nulls to be skipped in SUM.
>> MySQL skips errors just like NULLs.
>> PostgreSQL skips nulls and expression errors on sum(val+5)
>> PostgreSQL causes an error on casting error.
>> Addition with + and nulls does not work as sum over nulls.
>
> The last point I'm not sure sure that it's possible to tell is it? As
> SUM skips NULLs, how do you tell the difference?

The SQL standard says that aggregates can see the nulls and then choose 
what to do.  Sum() does not include it in the summation so it isn't as 
simple as sum is "+" over the group.  All the standard aggregates do this.

http://www.postgresql.org/docs/8.4/static/sql-expressions.html#SYNTAX-AGGREGATES

1+null ==> null

But sum() over 1,null ==> 1

To me, this means that the description of sum delegating to "+" isn't 
accurate as it's made some decisions on the way.  The semantics of sum 
are more than just those of "+" applied across the group.

	Andy

>
> - Steve
>

Received on Saturday, 14 November 2009 22:02:07 UTC