SQL中COUNT的小疑问。

来源：百度知道 编辑：UC知道 时间：2024/06/23 08:48:57
比如我有张表中，只有一列数据，它的值只有两种情况：0和1.
我怎么作出一张统计表，统计出0的个数，1的个数，并且分别占总数的百分比。用一条SQL查询语句得出。
回答者： zhujiapo - 二级

可能我说得不清楚，是0的个数，1的个数，0所占比例，1所占比例

Select sum(case when columnname=1 then 1 else 0 end) as '1的个数',sum(case when columnname=0 then 1 else 0 end) as '0的个数',cast(sum(case when columnname=1 then 1 else 0 end)/count(*)*100 as varchar)+'%' as '1所占的比例',cast(sum(case when columnname=0 then 1 else 0 end)/count(*)*100 as varchar)+'%' as '0所占的比例' from tablename

表 table 数据项 data

select count(*),count(*)/(select count(*) from table) from table group by data

不行吗？？我没去试过

select sum(zero) as zero,sum(one) as one,(sum(zero)+sum(one)) as he from(select (case when id=0 then count(*) else 0 end) as zero, (case when id=1 then count(*) else 0 end) as one from animal group by id)

这样就查出0,1,以及总和的了.剩下的就很简单了,相信你能搞定了.
都查出个数了以及总和了,算比例不是简单得不能再简单的事情了.
select sum(zero) as zero,sum(one) as one,(sum(zero)+sum(one)) as he ,sum(zero)/(sum(zero)+sum(one)) ,sum(one)/(sum(zero)+sum(one)) from(select (case when id=0 then count(*) else 0 end) as zero, (