How to use AVG, NVL, and MIN in one SELECT STMT

[msqueen0826]

Hello

I am trying to write a select statement that will compute the average room rate under the assumption that if the room rate is "null" it should be replaced by the minimum room rate from the table. The following is all the details of my table :

create table testing
SQL> rt_date  date,
  end_date    date,
  room_num    number(3),
  room_rate   number(5,2),
  state       varchar2(12)
)
/
insert into testing values
    ('10-APR-2010','15-MAY-2010',101,80,NULL)
/
insert into testing values
    ('1-JUN-2010','25-JUL-2010',103,NULL,'CALIFORNIA')
/
insert into testing values
    ('13-SEP-2010','15-JAN-2011',104,100,'NEW YORK')
/
insert into testing values
    ('17-NOV-2010','11-DEC-2010',202,120,'NEVADA')
/
insert into testing values
        (sysdate, sysdate, 205, null, null)
/
commit
/

and select statement appears as follow:

SQL> select * from testing;

START_DAT END_DATE    ROOM_NUM  ROOM_RATE STATE
--------- --------- ---------- ---------- ------------
10-APR-10 15-MAY-10        101         80
01-JUN-10 25-JUL-10        103            CALIFORNIA
13-SEP-10 15-JAN-11        104        100 NEW YORK
17-NOV-10 11-DEC-10        202        120 NEVADA
29-MAR-12 29-MAR-12        205

I would appreciate any help..
Thank You
Isabelle

[hfx642]

ONE Select statement. That would make things way too complicated.
This would be MUCH simpler...
(You could even put this into a DB function.)

Set Serveroutput on;

Declare

   Minimum_LVN Number;
   Total_LVN   Number;
   Count_LVN   Number;
   Average_LVN Number;

Begin
   DBMS_Output.Enable (1000000);

   Select Minimum (Room_Rate), Count (*)
   Into Minimum_LVN, Count_LVN
   From Testing;

   Select Nvl (Room_Rate, Minimum_LVN)
   Into Total_LVN
   From Testing;

   Average_LVN := Total_LVN / Count_LVN;
   DBMS_Output.Put_Line ('Average of ' || Count_LVN || ' rooms: ' || Average_LVN);
End;
/
Show Errors;