count() + case when统计问题

        在使用sql中统计数据中，因为要统计各个数量的数据，所以在count中使用case when。        

                ​​​​​​​        ​​​​​​​

统计语句如下：

SELECT t.name,count(*)  as total,count(case when status = 1 then 1 else 0 end) as using_count,count(case when status = 0 then 1 else 0 end) as stop_countfrom test_tablel tgroup by t.name

结果统计师有问题的：

测试发现，不管then 和else的返回多少都统计都是类似count(*)一样统计条数。如：

SELECT t.name,count(*)  as total,count(case when status = 1 then 5 else 0 end) as using_count,count(case when status = 0 then 1 else 3 end) as stop_countfrom test_tablel tgroup by t.name

再想到，count(*)是统计条数的，值为0是也属于一条数据。但是count()不会统计null数据。所以改为：

SELECT t.name,count(*)  as total,count(case when status = 1 then 1 else null end) as using_count,count(case when status = 0 then 1 else null end) as stop_countfrom test_tablel tgroup by t.name

或者不适用count函数，改为使用sum函数：

SELECT t.name,count(*)  as total,sum(case when status = 1 then 1 else 0 end) as using_count,sum(case when status = 0 then 1 else 0 end) as stop_countfrom test_tablel tgroup by t.name