Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Aggregate functions in case of Columns having Nulls

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sats456

New User


Joined: 29 May 2005
Posts: 12

PostPosted: Wed Jul 20, 2005 2:33 pm    Post subject: Aggregate functions in case of Columns having Nulls
Reply with quote

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 ?
Back to top
View user's profile Send private message

rssomm

New User


Joined: 05 Jun 2005
Posts: 41
Location: Hyderabad

PostPosted: Wed Jul 20, 2005 3:26 pm    Post subject:
Reply with quote

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


Madhu
Back to top
View user's profile Send private message
narasimha_devi
Warnings : 1

New User


Joined: 10 Mar 2005
Posts: 26

PostPosted: Mon Jul 25, 2005 2:05 pm    Post subject: Re: Aggregate functions in case of Columns having Nulls
Reply with quote

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


thaks in advance
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon Jul 25, 2005 2:14 pm    Post subject: Re: Aggregate functions in case of Columns having Nulls
Reply with quote

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.
Back to top
View user's profile Send private message
narasimha_devi
Warnings : 1

New User


Joined: 10 Mar 2005
Posts: 26

PostPosted: Tue Jul 26, 2005 6:27 pm    Post subject: Re: Aggregate functions in case of Columns having Nulls
Reply with quote

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

thank u very much onceagain
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Summing on Mixed Case Literals Roy Ware SYNCSORT 3 Tue Jan 03, 2017 10:18 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Regd Date Functions in Syncsort muralikrishnan_new SYNCSORT 2 Fri Jul 01, 2016 8:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us