Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: are the aggregated columns taken as null?

    Hi ,

    Are the aggregated columns taken as null in DB2. like if i define sum(col1) will this be considered as null or not null column (if the column is defined as not null)

    Thanks,
    Waseem

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    sum(col1) is considered nullable even if col1 is NOT NULL.

    ( if the resultset is empty, sum(col1) will be NULL )

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    and what would be the result if the aggregating column something like this sum(10 + null) as column1?

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ahmedwaseem2000
    and what would be the result if the aggregating column something like this sum(10 + null) as column1?
    Also NULL: SUM(expr) only includes rows for which the expression is not NULL. If there is at least one such row, the result is not NULL, otherwise it is.

    With AVG(expr) it's even more apparent: the denominator will be the number of rows for which expr is not NULL.
    Actually, the same holds for any of the other aggregate functions:
    COUNT(expr) only counts the number of not NULL expr's.
    In that respect, COUNT(*) is actually equivalent to COUNT(1).
    MIN(expr) and MAX(expr) equally ignore NULL expressions.
    All (except for COUNT(expr) of course) return NULL when applied on an empty set, i.e., either an empty table (after having applied the WHERE condition), or an empty list (after removal of the NULL expressions).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •