sql关于avg中的else null和else 0

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)      一般来说这种形式用不到