求按条件统计计数的sql
我有一表t1(id,fid,yesno)
id为自动编号,yesno为bit型
id fid yesno
1 1 1
2 1 0
3 0 1
4 0 1
5 1 1
要求按fid 分组分别统计出 yesno为1和0的记录数? 如上表统计结果应这样
fid yesno=1 yesno=0
1 2 1
0 2 0
[解决办法]
select fid,
sum(case when yesno = 1 then 1 else 0 end) 'yesno = 1 ',
sum(case when yesno = 0 then 1 else 0 end) 'yesno = 0 '
from t1
group by fid
[解决办法]
Select fid,
Sum(Case yesno When 1 then 1 Else 0 End) As 'yesno=1 ',
Sum(Case yesno When 0 then 1 Else 0 End) As 'yesno=0 '
From t1
Group By fid
[解决办法]
不重復了,
[解决办法]
declare @table table (fid int,yesno bit)
insert into @table select 1,1
union all select 1,0
union all select 0,1
union all select 0,1
union all select 1,1
select fid,yesno1=sum(case when yesno=1 then 1 else 0 end),
yesno0=sum(case when yesno=0 then 1 else 0 end)
from @table group by fid
020
121
