3 Replies Latest reply: Mar 4, 2013 7:45 AM by Steve42 RSS

    Need get average of numbers>0

      I need to get the average of 2 POSITIVE numbers, not null.
      I still want to keep the NULL in the column. I want to update the table
      where NVL(temp_s,-1)=0
      Here is a sample:
      create table dummy (MY_ID NUMBER(5,0)
      ,C_DATE DATE
      ,TEMP_S NUMBER(4,0) );

      Insert into dummy(my_id,c_date,temp_s) values(41,to_date('20130114','YYYYMMDD') ,32);
      Insert into dummy(my_id,c_date,temp_s) values(41,to_date('20130113','YYYYMMDD') ,0);
      Insert into dummy(my_id,c_date,temp_s) values(41,to_date('20130112','YYYYMMDD') ,57);
      Insert into dummy(my_id,c_date,temp_s) values(41,to_date('20130111','YYYYMMDD') ,NULL);
      Insert into dummy(my_id,c_date,temp_s) values(56,to_date('20130110','YYYYMMDD') ,59);
      Insert into dummy(my_id,c_date,temp_s) values(56,to_date('20130109','YYYYMMDD') ,0);
      Insert into dummy(my_id,c_date,temp_s) values(56,to_date('20130108','YYYYMMDD') ,51);
      Insert into dummy(my_id,c_date,temp_s) values(56,to_date('20130107','YYYYMMDD') ,NULL);

      I want the output to be:
      41     14-JAN-13     32
      41     13-JAN-13     45 <=== this to be updated based on the day before and after.
      41     12-JAN-13     57
      41     11-JAN-13     NULL
      56     10-JAN-13     59
      56     09-JAN-13     55 <=== this to be updated based on the day before and after.
      56     08-JAN-13     51
      56     07-JAN-13     NULL
        • 1. Re: Need get average of numbers>0
          Still haven't figured out
          Instructions here: https://forums.oracle.com/forums/help.jspa                                                                                                                                                                                                                
          • 2. Re: Need get average of numbers>0
            Frank Kulash

            Here's one way:
            MERGE INTO     dummy     dst
            USING   (
                     SELECT  my_id, c_date
                     ,         CASE
                                 WHEN  temp_s <= 0
                             THEN  CEIL ( ( LAG  (temp_s) OVER ( PARTITION BY  my_id
                                                                          ORDER BY       c_date
                                          + LEAD (temp_s) OVER ( PARTITION BY  my_id
                                                                          ORDER BY       c_date
                                  / 2
                              END          AS temp_s
                     FROM    dummy
                 )          src
            ON     (    dst.my_id     = src.my_id
                 AND  dst.c_date     = srC.c_date
            SET     dst.temp_s     = src.temp_s
            WHERE     dst.tEmp_s     <= 0
            Depending on what role my_id plays in this problem, and what is unique, you may need to change this a little.
            • 3. Re: Need get average of numbers>0
              Thanks. Frank.

              As for the {_code_}, it would be nice if the web designer could put that info on the
              far right side of the page in the "Plain Text Help" box...

              Just a suggestion...