sum 与 count 配合case when 的区别

案例表：

create schema test;
create table test.student(id int4, name varchar(20), score int4, course int4, grade int4, sex int2);
 insert into test.student(id, name, score, course, grade, sex) values(1, 'alice', 60, 1,  6, 0), (2,'bob', 60, 1,  6, 0);
insert into test.student(id, name, score, course, grade, sex) values(3, 'cark', 80, 1, 6 , 0);
insert into test.student(id, name, score, course, grade, sex) values(4, 'dock', 100,1,6,0);
insert into test.student(id, name, score, course, grade, sex) values(5,'elic', 70, 1, 5, null);
insert into test.student(id, name, score, course, grade, sex) values(6,'frank', 70, 2, 5, null);
insert into test.student(id, name, score, course, grade, sex) values(7,'grace', 70, 2, 4, null);
insert into test.student(id, name, score, course, grade, sex) values(8,'hill', 60, 1, 4, 1);
insert into test.student(id, name, score, course, grade, sex) values(9,'iris', 80, 2, 4, 1);
insert into test.student(id, name, score, course, grade, sex) values(10,'jacky', 80, 2, 4, 1);

#select * from test.student order by id;
 id | name  | score | course | grade | sex 
----+-------+-------+--------+-------+-----
  1 | alice |    60 |      1 |     6 |   0
  2 | bob   |    90 |      1 |     6 |   0
  3 | cark  |    80 |      1 |     6 |   0
  4 | dock  |   100 |      1 |     6 |   0
  5 | elic  |    70 |      1 |     5 |    
  6 | frank |    70 |      2 |     5 |    
  7 | grace |    50 |      2 |     4 |    
  8 | hill  |    60 |      1 |     4 |   1
  9 | iris  |    80 |      2 |     4 |   1
 10 | jacky |    80 |      2 |     4 |   1
(10 rows)

1、计算全年级总人数，男人数，女生数及性别未知数

方法一：
-- 感谢评论区 AetherTL的反馈
-- 已把 “count(case when sex <> 1 and sex <> 2 then 1 end) 性别未知“ 错误语句修改为“count(case when sex is null then 1 end) 性别未知”。切记不可修改为“count(case when sex <>0 and sex <> 1 then 1 end) 性别未知”， 因为count(sex) 是不会统计为null的元素
-- 为什么 错误语句 “count(case when sex <> 1 and sex <> 2 then 1 end)“ 统计结果为4呢？
-- 因为这句相当于 “count(case when sex = 0 then 1 end)”。
select
count(1) 总人数,
count(case when sex = 0 then 1 end) 男生数,
count(case when sex = 1 then 1 end) 女生数,
count(case when sex is null then 1 end) 性别未知 -- 写成 count(case when sex <>0 and sex <> 1 then 1 end) 还是为空，因为count(sex) 是不会统计为null的元素 
from
test.student;
 总人数 | 男生数 | 女生数 | 性别未知
--------+--------+--------+----------
     10 |      4 |      3 |        3

更正为：
select
    count(1) 总人数,
    sum(case when sex = 0 then 1 end) 男生数,
    sum(case when sex = 1 then 1 end) 女生数,
    sum(case when sex is null then 1 end) 性别未知
from
    test.student;
 总人数 | 男生数 | 女生数 | 性别未知
--------+--------+--------+----------
     10 |      4 |      3 |        3

2、计算四五六三个年级分别的男生数、女生数及性别未知数

方法一：
select
    grade,
    count(1) 总人数,
    sum(case when sex = 0 then 1 else 0 end) 男生数,
    sum(case when sex = 1 then 1 else 0 end) 女生数,
    sum(case when sex is null then 1 else 0 end) 性别未知
from 
    test.student 
group by grade order by grade desc;
 grade | 总人数 | 男生数 | 女生数 | 性别未知
-------+--------+--------+--------+----------
     6 |      4 |      4 |      0 |        0
     5 |      2 |      0 |      0 |        2
     4 |      4 |      0 |      3 |        1
(3 rows)
     
方法二，还能继续使用count 完成嘛？
select
    grade,
    count(1) 总人数,
    count(case when sex = 0 then 1 end) 男生数,
    count(case when sex = 1 then 1 end) 女生数,
    count(case when sex is null then 1 end) 性别未知
from 
    test.student 
group by grade order by grade desc;
 grade | 总人数 | 男生数 | 女生数 | 性别未知
-------+--------+--------+--------+----------
     6 |      4 |      4 |      0 |        0
     5 |      2 |      0 |      0 |        2
     4 |      4 |      0 |      3 |        1
(3 rows)

select
    grade,
    count(1) 总人数,
    count(case when sex = 0 then 1 else 0 end) 男生数,
    count(case when sex = 1 then 1 else 0 end) 女生数,
    count(case when sex is null then 1 else 0 end) 性别未知
from 
    test.student 
group by grade order by grade desc;
 grade | 总人数 | 男生数 | 女生数 | 性别未知 
-------+--------+--------+--------+----------
     6 |      4 |      4 |      4 |        4
     5 |      2 |      2 |      2 |        2
     4 |      4 |      4 |      4 |        4

看完上边的例子，说说count(1) 与 count(列或表达式)时的差别，count(1)会全部列都计算其内，count(列或表达式)不会将null计入其中。
sum也是如此，例如有某表字段F有三列，A，B，C
三列都不为空，则sum(A+B+C)
假如其中有空值，比如说是A， 则 sum会忽略A值，最终结果为 sum(B+C)

最终结论：只要条件合理，count case when 与 sum的结果是一致的。