求一个group by 句子

[guy1218]

表大致如下：

Gender Section
M 1
M 1
F 1
F 2
M 2
M 1
M 2
F 3
M 2
M 3
F 3
....

我用

select section, count(*) as MaleNum from table
group by section
having Gender = 'M'

求每个班的男生数量，可是好像不行，语法错误。我又想不出哪里错了。。
帮帮忙吧！

[marco08]

select section, count(*) as MaleNum from table
where Gender = 'M'
group by section

[marco08]

create table T(Gender char(1), [Section] int)
insert T select 'M', 1
union all select 'M', 1
union all select 'F', 1
union all select 'F', 2
union all select 'M', 2
union all select 'M', 1
union all select 'M', 2
union all select 'F', 3
union all select 'M', 2
union all select 'M', 3
union all select 'F', 3

select section, count(*) as MaleNum from T
where Gender = 'M'
group by section

--result
section MaleNum
----------- -----------
1 3
2 3
3 1

(3 row(s) affected)

[zsl5305256]

select section, count(*) as MaleNum from table
where Gender = 'M'
group by section

[guy1218]

问题是如果某个section没有男生，那你这个就不会返回任何结果。我要的结果是0。

[marco08]

create table T(Gender char(1), [Section] int)
insert T select 'M', 1
union all select 'M', 1
union all select 'F', 1
union all select 'F', 2
union all select 'M', 2
union all select 'M', 1
union all select 'M', 2
union all select 'F', 3
union all select 'M', 2
union all select 'M', 3
union all select 'F', 3
union all select 'F', 4 --新加的记录

select distinct [Section],
MaleNum=(select count(*) from T where [Section]=tmp.[Section] and Gender='M')
from T as tmp

--result
Section MaleNum
----------- -----------
1 3
2 3
3 1
4 0

(4 row(s) affected)

[guy1218]

嗯，可以了。谢谢啦！