create table ttt as
select * from (
select 1 as id,'2' as col1,'10%' as col2 from dual union
select 2 as id,'2' as col1,'10%' as col2 from dual union
select 3 as id,'3' as col1,'10%' as col2 from dual union
select 4 as id,'4' as col1,'10%' as col2 from dual union
select 5 as id,'9' as col1,'20%' as col2 from dual );
select * from ttt;
select c1 / (case
when instr((select col1 from ttt where rownum = 1), '%') = 0 then
1
else
(select count(1) from ttt)
end) || (case
when instr((select col1 from ttt where rownum = 1), '%') = 0 then
''
else
'%'
end) as col1,
c2 / (case
when instr((select col2 from ttt where rownum = 1), '%') = 0 then
1
else
(select count(1) from ttt)
end) || (case
when instr((select col2 from ttt where rownum = 1), '%') = 0 then
''
else
'%'
end) as col2
from (select sum(replace(col1, '%', '')) as c1,
sum(replace(col2, '%', '')) as c2
from ttt)
SQL> select sum(case when instrb(col1,'%')=0 then col1 end) sum_col1,
2 to_char(avg(case when instrb(col2,'%')>0 then replace(col2,'%','') end))||'%' avg_col2
3 from test;