有个求SUM的问题，比较特殊，求助

ID       col1       col2       后面还有列col3,col4....
1           2             10%
2           2             10%
3           3             10%
4           4             10%
5           9             20%

对于列col1,col2,col3   ，要么全是数值，要么全是百分比，具体每列是什么，不确定。
要求：如果是数值的列进行SUM   ，如果是百分比的列求平均值，还需要%

例如查询结果如下
col1     col2  
20         12%

此SQL，如何写，谢谢


------解决方案--------------------
请告知每列的数据类型
------解决方案--------------------
只能一个字段一个字段的写了,问题是%的格式应该是字符型的吧?这个写起来比较麻烦.
------解决方案--------------------
select sum(to_number(col1)) as col1,avg(to_number(replace(col2, '% ', ' '))|| '% ' as col2 from xxx
------解决方案--------------------
SQL> select * from test;

COL1 COL2
---------- ----------
2 10%
2 10%
3 10%
4 10%
9 20%

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;

SUM_COL1 AVG_COL2
---------- -----------------------------------------
20 12%

SQL>
------解决方案--------------------
select sum(to_number(col1)) as col1,avg(to_number(replace(col2, '% ', ' '))|| '% ' as col2 from xxx


------解决方案--------------------
col1,col2,col3 是数值还是百分比不确定的情况下可以这样

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)