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
insert into testing values
insert into testing values
    ('13-SEP-2010','15-JAN-2011',104,100,'NEW YORK')
insert into testing values
insert into testing values
        (sysdate, sysdate, 205, null, null)

and select statement appears as follow:

SQL> select * from testing;

--------- --------- ---------- ---------- ------------
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

Member Avatar

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;


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

   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);
Show Errors;
commented: agree +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.