drop table test1;
use sys;
create table test1
(
id int not null,
num int not null
);
insert into test1 values (1,10);
insert into test1 values (2,20);
insert into test1 values (3,30);
insert into test1 values (4,40);
insert into test1 values (5,50);
select
avg(case when id < 5 then num else null end) as avg1
,avg(case when id < 5 then num else 0 end) as avg2
,avg(num) as avg3
from
test1;
结果
avg1=25=(10+20+30+40)/4
avg2=20=(10+20+30+40+0)/5
avg3=30=(10+20+30+40+50)/5
所以else null并不对不满足条件的计数(分母不包含)
但是else 0 包含
但是sum
select
sum(case when id < 5 then num else null end) as avg1
,sum(case when id < 5 then num else 0 end) as avg2
,sum(num) as avg3
from
test1;
sum1=100=(10+20+30+40)
sum2=100=(10+20+30+40+0)
sum3=120=(10+20+30+40+50)
再看count
select
count(case when id < 5 then num else null end) as avg1
,count(case when id < 5 then num else 0 end) as avg2
,count(num) as avg3
from
test1;
count1=4
count2=5
count3=5
结论:
avg:else null = sum(else null/else0)/count(else null) 常用
else 0 = sum(else null/else0)/count(else 0) 一般来说这种形式用不到