Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: help with a query...

    Hi guys

    I wrote the below query comparing the output between using and not using NVL(counter,0). I'm getting different values for some corresponding rows.

    Can't see where I'm going wrong. Will appreciate if someone could help.

    Code:
    (SELECT DATETIME,
     ROUND(SUM(COUNTER1+COUNTER2)/AVG(COUNTER3),2) as ABC,
     ROUND(SUM(NVL(COUNTER1,0)+NVL(COUNTER2,0))/AVG(NVL(COUNTER3,0)),2) as ABC1
    FROM
    SCHEMA.TABLE
    WHERE
      DATETIME BETWEEN TO_DATE('11-02-10 00:00', 'dd-mm-yy hh24:mi') AND TO_DATE('11-02-10 23:59', 'dd-mm-yy hh24:mi') 
    GROUP by datetime)
    Regards

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Hi,
    most aggregate functions (SUM and AVG among them) ignore NULL. It affects AVG, as AVG(2,4,NULL)=3 and AVG(2,4,0)=2.
    You might easily confirm it by running partial results (and knowing, that AVG=SUM/CNT):
    Code:
    SELECT SUM(COUNTER3), COUNT(COUNTER3), AVG(COUNTER3),
      COUNT(DECODE(COUNTER3,NULL,1)), -- number of NULLs in COUNTER3
      SUM(NVL(COUNTER3,0)), COUNT(NVL(COUNTER3,0)), AVG(NVL(COUNTER3,0))
    FROM schema.table
    WHERE <condition on datetime>;
    The "correct" result depends on requirements, of which nobody except you is aware.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    I'd like to close this call as I found out that

    COUNTER3 is NULL for those rows where

    AVG(COUNTER3) and AVG(NVL(COUNTER3,0)) are not the same. And the NULL fields are not being taken into account when the AVG is being computed. However, if the NULL field is replaced with a zero, then the field is being taken into account.

Posting Permissions

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