How to perform AVG on numbers only 1

[fiat2]

using Oracle 9.2

I've got a table that has a field (Result) that is set to VarChar2. Occasionaly, the data in this field are characters but other times it's numbers.
Is there a way to perform an AVG on this field eventhough sometimes it contains a character.

For example

Sample    Parameter     Result
1          Diameter      13
2          Diameter      12.25
3          Diameter      NR
4          Diameter      12.22
1          Inspec        P
2          Inspec        F
3          Inspec        P
4          Inspec        P

So, I'd like to perform an AVG on Diameter. The results could contain many non-numeric values, i.e. NR, NA, *, etc...

Thanks!!

 

[SantaMufasa]

Fiat,

Perhaps the easiest method is to use a user-defined function:

create or replace function Num_check (x varchar2) return number is
    num_hold number;
begin
    num_hold := x;
    return num_hold;
exception
    when others then
        return null;
end;
/

Function created.

select * from fiat;

SAMPLE PARAMETER  RESULT
------ ---------- ------
     1 Diameter   13
     2 Diameter   12.25
     3 Diameter   NR
     4 Diameter   12.22
     1 Inspec     P
     2 Inspec     F
     3 Inspec     P
     4 Inspec     P

8 rows selected.

select parameter,avg(num_check(result)) average
from fiat
where parameter = 'Diameter'
group by parameter
/

PARAMETER     AVERAGE
---------- ----------
Diameter        12.49

1 row selected.

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[fiat2]

works like a charm.