Aggregate functions in case of Columns having Nulls

sats456 · New User Joined: 29 May 2005 Posts: 12

Hi All,
Suppose I have a DB2 Table having one of column as Amount.This column has been declared to have Null Values allowed in it.

FOllowing are my Queries : -

1) Wat value will be returned when I say MAX (Amount ) assuming there are 5 rows in the table having values as

100
200
Null
300
400

in the Amount field ? Will it Abend or value 400 be returned ?
Similarly will SUM return value 1000 or Abend ?

rssomm · New User Joined: 05 Jun 2005 Posts: 41 Location: Hyderabad

Max Value would be 400 and sum is 1000. There will be no ABEND.

Madhu

narasimha_devi · New User Joined: 10 Mar 2005 Posts: 26

very fine answer.but what abt the averagre value.does null is cosidered r not.plesae clarify

thaks in advance

ovreddy · Posted: Mon Jul 25, 2005 2:14 pm

Hi,

The AVG function is applied to the set of values derived from the argument values by the elimination of null values. So only the not null values are counted.

Thanks,
Reddy.

narasimha_devi · New User Joined: 10 Mar 2005 Posts: 26

thank u very much.i got answers for allquestions and its surprise that theperson is same inboth thecases.

thank u very much onceagain