Member Avatar for 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

Edited by Dani because: Formatting fixed
  • 2 Contributors
  • 1 Reply
  • 491 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Member #647493

Dani AI

Generated

— you can do this in one plain SELECT by replacing NULL rates with the table minimum inside AVG. A concise form uses a scalar subquery:

SELECT ROUND(AVG(NVL(room_rate, (SELECT MIN(room_rate) FROM testing))), 2) AS avg_rate
FROM testing;

For readability and to ensure the MIN is computed only once (better on large tables), compute it in a derived table or WITH:

SELECT ROUND(AVG(NVL(t.room_rate, m.min_rate)), 2) AS avg_rate
FROM testing t
CROSS JOIN (SELECT MIN(room_rate) AS min_rate FROM testing) m;

Notes and edge cases:

  • AVG normally ignores NULLs; using NVL forces NULL rows to be counted with the MIN value.
  • If every row has NULL for room_rate then MIN(...) is NULL and the replacements remain NULL; wrap the MIN in NVL if you want a concrete fallback (for example NVL((SELECT MIN(...)), 0)).
  • ROUND is optional — use it if you want fixed decimal precision.

Regarding ’s PL/SQL suggestion: the set-based SQL above is simpler and faster for this task. If you prefer PL/SQL, be careful when aggregating — replacing NULLs per row then summing requires SUM(NVL(...)) rather than selecting the NVL expression into a scalar (that would attempt to fetch many rows). The SELECT + CROSS JOIN approach avoids that class of bug and keeps everything in a single, readable statement.

Member Avatar for Member #647493

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;
Edited by Member #647493
commented: agree +13
Be a part of the DaniWeb community

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