请教一条简单的单表复合分组聚合查询语句

[神马被哪个妞占用了呢]

表：
A          B
0          0
1          1
2          1
0          1
3          1
0          0
2          0


统计结果为：
A     B为0的数量     总数
0     2              3
1     0              1
2     1              2
3     0              1

[神马被哪个妞占用了呢]

select A,sum(case when B =0 then 1 else 0 end)B为0的次数,sum(case when （B =0 OR B=1) then 1 else 0 end)总次数 from tab group by A

用这个语句，结果集中A列有许多重复的数量都为0的……求大神解答

[koumingjie]

create table tb
(
  A int,
  B int
)

insert into tb 
select 0,0 union all 
select 1,1 union all 
select 2,1 union all 
select 0,1 union all 
select 3,1 union all 
select 0,0 union all 
select 2,0 


select A,sum(case when B=0 then 1 else 0 end) B为0的数量,COUNT(B) 总数 from tb group by A

[koumingjie]

create table tb
(
  A int,
  B int
)

insert into tb 
select 0,0 union all 
select 1,1 union all 
select 2,1 union all 
select 0,1 union all 
select 3,1 union all 
select 0,0 union all 
select 2,0 


select A,sum(case when B=0 then 1 else 0 end) B为0的数量,COUNT(B) 总数 from tb group by A

A           B为0的数量      总数
----------- ----------- -----------
0           2           3
1           0           1
2           1           2
3           0           1

[开着拖拉机泡妞]

--> 测试数据：[A1]
if object_id('[A1]') is not null drop table [A1]
create table [A1]([A] int,[B] int)
insert [A1]
select 0,0 union all
select 1,1 union all
select 2,1 union all
select 0,1 union all
select 3,1 union all
select 0,0 union all
select 2,0

SELECT A,SUM(CASE B WHEN 0 THEN 1 ELSE 0 END) AS [B为0的次数],
COUNT(1) AS [B的总次数]
from A1 GROUP BY A
--你的语句修改后:
select A,sum(case when B =0 then 1 else 0 end) B为0的次数,
sum(case when B =0 OR B=1 then 1 else 0 end) 总次数 
from A1 group by A
/*
A	B为0的次数	B的总次数
0	2	3
1	0	1
2	1	2
3	0	1
*/

[神马被哪个妞占用了呢]

谢谢楼上各位大侠