直接用条件把等于0的过滤掉然后用AVG函数不就可以了 select avg(col) from table where col<> 0
你這樣是不行的啊! select sum/count from (select sum(a1) sum from a) a,(select count(a1) count from a where nvl(a1,0)<>0) b
SORRY 這種方式也是可以的啊!
用decode函数处理下再avg。 with t as ( select 20 id from dual union all select 30 from dual union all select 0 from dual union all select 40 from dual union all select 110 from dual union all select 0 from dual ) select avg(decode(id,0,null,id)) from t;
select avg(case when id = 0 then null else id end) from t;
欢迎加入oracle qq群9701750
1 20
2 30
3 0
4 40
5 110我想求A1的平均值,如果AVG(A1)的话就是(20+30+0+40+110)/5=40,
而我想算不包含0的数据,也就是(20+30+40+110)/4=50。
请问要怎么才能解决这个问题。有没函数呢?我不想再判断,因为数据项很多!太麻烦了。
select avg(col) from table where col<> 0
select sum/count from
(select sum(a1) sum from a) a,(select count(a1) count from a where nvl(a1,0)<>0) b
SORRY
這種方式也是可以的啊!
with t as (
select 20 id from dual
union all
select 30 from dual
union all
select 0 from dual
union all
select 40 from dual
union all
select 110 from dual
union all
select 0 from dual
)
select avg(decode(id,0,null,id)) from t;