This discussion is archived
3 Replies Latest reply: Mar 4, 2013 5:45 AM by Steve42 RSS

Need get average of numbers>0

Steve42 Newbie
Currently Being Moderated
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:
<code>
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);

</code>
I want the output to be:
<code>
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
</code>
TIA
Steve42
  • 1. Re: Need get average of numbers>0
    SomeoneElse Guru
    Currently Being Moderated
    Still haven't figured out
     tags?
    
    Instructions here: https://forums.oracle.com/forums/help.jspa                                                                                                                                                                                                                
  • 2. Re: Need get average of numbers>0
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    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
         )
    WHEN MATCHED THEN UPDATE
    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
    Steve42 Newbie
    Currently Being Moderated
    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...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points