求按条件统计计数的sql

[clkun]

我有一表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

[dawugui]

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

[dawugui]

create table t1 (id int,fid int,yesno int)
insert into t1 values(1, 1, 1)
insert into t1 values(2, 1, 0)
insert into t1 values(3, 0, 1)
insert into t1 values(4, 0, 1)
insert into t1 values(5, 1, 1)

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

drop table t1
/*
fid yesno = 1 yesno = 0
----------- ----------- -----------
0 2 0
1 2 1

（所影响的行数为 2 行）
*/

[SoftwKLC]

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

[fa_ge]

不重復了,

[welove1983]

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


0 2 0
1 2 1