表aaa
字段 bbb ......
0
0
0
1
1
0
现在我需要在一个查询中统计出bbb的总数，bbb=1的总数，以及bbb=1占的比例，不知道该怎么办
------解决方案--------------------
- SQL code
select count(bbb) bbb_cnt, count(case when bbb=1 then 1 else null end) bbb_1_cnt, count(case when bbb=0 then 1 else null end) bbb_0_cnt, rtrim(count(case when bbb=1 then 1 else null end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent from tb 或select count(bbb) bbb_cnt, sum(case when bbb=1 then 1 else 0 end) bbb_1_cnt, sum(case when bbb=0 then 1 else 0 end) bbb_0_cnt, rtrim(sum(case when bbb=1 then 1 else 0 end)/isnull(nullif(count(bbb)*100),0),1) + '%' bbb_1_percent from tb
------解决方案--------------------
- SQL code
create table aaa( bbb int)insert into aaa values(0) insert into aaa values(0) insert into aaa values(0) insert into aaa values(1) insert into aaa values(1) insert into aaa values(0) goselect bbb等于1的总数 = count(*) , bbb等于1所占的比例 = cast(count(*)*1.0/(select count(*) from aaa) as decimal(18,2)) from aaa where bbb = 1drop table aaa/*bbb等于1的总数 bbb等于1所占的比例 ----------- -------------------- 2 .33（所影响的行数为 1 行）*/
------解决方案--------------------
某个bbb为null
或
所有bbb均为null的情况需要考滤
------解决方案--------------------
- SQL code
create table aaa( bbb int)insert into aaa values(0) insert into aaa values(0) insert into aaa values(0) insert into aaa values(1) insert into aaa values(1) insert into aaa values(0) goselect bbb等于1的总数 = count(*) , bbb等于1所占的比例 = cast(cast(count(*)*100.00/(select count(*) from aaa) as decimal(18,2)) as varchar) + '%' from aaa where bbb = 1drop table aaa/*bbb等于1的总数 bbb等于1所占的比例 ------------- ------------------------------- 2 33.33%（所影响的行数为 1 行）*/
------解决方案--------------------
- SQL code
select count(*) 总数,sum(case bbb when 1 then 1 else 0 end) 等于1的个数,sum(case bbb when 1 then 1 else 0 end)*1.0/count(*)比例 from sss
------解决方案--------------------
- SQL code
create table aaa( bbb int)insert into aaa values(0) insert into aaa values(0) insert into aaa values(0) insert into aaa values(1) insert into aaa values(1) insert into aaa values(0) goselect count(bbb) bbb_cnt, sum(case when bbb=0 then 1 else null end) bbb_0_cnt, sum(isnull(bbb,0)) bbb_1_cnt, rtrim(cast(sum(isnull(bbb,0))* 100.0/count(*) as numeric(12,2)) ) + '%' bbb_1_percent from aaa/*bbb_cnt bbb_0_cnt bbb_1_cnt bbb_1_percent ----------- ----------- ----------- ----------------------------------------- 6 4 2 33.33%（所影响的行数为 1 行）警告: 聚合或其它 SET 操作消除了空值。*/drop table aaa