求平均值

[ttyp]

表T
字段A，B，C
有三条记录

A B C
0 20 30
10 0 0
60 90 0

求A，B，C的平均值，0的记录不做统计，如A列，结果为（10+60）/2

要求一条语句实现

[冷箫轻笛]

菜大侠题意理解的有点问题哈

[ttyp]

有个缺陷，sum(case when A=0 then 0 else 1 end)为0的话报错

[青锋-SS]

问题不小,全过滤掉了.

[子陌红尘]

select
sum(A)/(case sum(case A when 0 then 0 else 1 end) when 0 then 1 else sum(case A when 0 then 0 else 1 end) end),
sum(B)/(case sum(case B when 0 then 0 else 1 end) when 0 then 1 else sum(case B when 0 then 0 else 1 end) end),
sum(C)/(case sum(case C when 0 then 0 else 1 end) when 0 then 1 else sum(case C when 0 then 0 else 1 end) end)
from
T

[zlp321002]

SELECT DISTINCT
A=(SELECT AVG(A) FROM 表 WHERE A>0),
B=(SELECT AVG(B) FROM 表 WHERE B>0),
C=(SELECT AVG(C) FROM 表 WHERE C>0)
FROM 表

[qw12cn]

select sum(a)/(select count(*) from abc where a<>0) as avg_a,
sum(b)/(select count(*) from abc where b<>0) as avg_b,
sum(c)/(select count(*) from abc where c<>0) as avg_c
from abc

[子陌红尘]

declare @t table(A int,B int,C int)
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0


select
sum(A)/(case sum(case A when 0 then 0 else 1 end) when 0 then 1 else sum(case A when 0 then 0 else 1 end) end) as A,
sum(B)/(case sum(case B when 0 then 0 else 1 end) when 0 then 1 else sum(case B when 0 then 0 else 1 end) end) as B,
sum(C)/(case sum(case C when 0 then 0 else 1 end) when 0 then 1 else sum(case C when 0 then 0 else 1 end) end) as C
from
@t

/*
A B C
----------- ----------- -----------
35 55 30
*/

[caixia615]

select (sum(A)/(select count(A) from tablename where A>0))AS A,(sum(B)/(select count(B) from tablename where B>0))AS B,(sum(C)/(select count(C) from tablename where C>0))AS C from tablename

[冷箫轻笛]

select case sum(a) when 0 then 0 else sum(a)/sum(case a when 0 then 0 else 1 end) end as a,
case sum(b) when 0 then 0 else sum(b)/sum(case b when 0 then 0 else 1 end) end as b,
case sum(c) when 0 then 0 else sum(c)/sum(case c when 0 then 0 else 1 end) end as c,
from t

[冷箫轻笛]

zlp321002(众里寻它千百度，蓦然回首，那人却在灯火阑珊处。)

厉害阿厉害！

[子陌红尘]

借鉴zlp321002的：
-------------------------------------------------------------------
declare @t table(A int,B int,C int)
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0


SELECT
A=isnull((SELECT AVG(A) FROM @t WHERE A<>0),0),
B=isnull((SELECT AVG(B) FROM @t WHERE B<>0),0),
C=isnull((SELECT AVG(C) FROM @t WHERE C<>0),0)

/*
A B C
----------- ----------- -----------
35 55 30
*/

[caixia615]

declare @t table(A int,B int,C int)
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0
select (sum(A)/(select count(A) from @t where A>0))AS A,(sum(B)/(select count(B) from @t where B>0))AS B,(sum(C)/(select count(C) from @t where C>0))AS C from @t
结果:
-------------------------
A B C
----------- ----------- -----------
35 55 30

（所影响的行数为 1 行）

[ttyp]

谢谢诸位，我也是使用libin_ftsafe(子陌红尘：当libin告别ftsafe)的方法，
zlp321002(众里寻它千百度，蓦然回首，那人却在灯火阑珊处。) 和qw12cn() 的由于表T的条件复杂（是我简化了）不做考虑，结帐了

[BEN_717_GG]

SELECT DISTINCT
A=(SELECT AVG(A) FROM temp WHERE A<>0),
B=(SELECT AVG(B) FROM temp WHERE B<>0),
C=(SELECT AVG(C) FROM temp WHERE C<>0)
FROM temp

[qw12cn]

子陌红尘的好

[秋水森]

create table T
(
a int,
b int,
c int
)
go
insert into T (a,b,c) values(0,20,30)
go
insert into T(a,b,c) values(10,0,0)
go
insert into T(a,b,c) values(60,90,0)
go
select sum(a)/sum(case a when 0 then 0 else 1 end) as a,
sum(b)/sum(case b when 0 then 0 else 1 end) as b,
sum(c)/sum(case c when 0 then 0 else 1 end) as c from T
go
大家可以试一下这种算法，这个问题不难！！！

[caijiek]

create table test (A int,B int, C int)


insert into test
select 0,20,30
union all
select 10,0,0
union all
select 60,90,0

select sum(A)/sum(case when A=0 then 0 else 1 end) as AverageA,
sum(B)/sum(case when B=0 then 0 else 1 end) as AverageB,
sum(C)/sum(case when C=0 then 0 else 1 end)as AverageC
from test

[cnrefresh]

store

[zibetqzy]

dd

[xihii6]

ding