我有这样一个表,应该怎样建立查询呢?

[pinyu]

考试证号 成绩 班级
xx00001 145 1班
xx00002 248 1班
. . .
. . .
. . .
xx34590 212 5班
. . .

现在我要得到这样的结果
按班级统计出，该班总人数是多少，150-180分是多少，180-210分的是多少人等
最后需要的表如下
班级 实考人数 col150to180 col180to210
1班 54 23 15
2班 53 16 18
. . . .


请各位大侠不吝赐教。

[lsxaa]

select 班级,count(*) as 实考人数,
sum(case when 成绩>150 and 成绩<180 then 1 else 0 end) as col150to180,
sum(case when 成绩>180 and 成绩<210 then 1 else 0 end) as col150to180
from t
group by 班级

[haoK]

SELECT 班级, COUNT(*) AS 实考人数,
SUM(
CASE
WHEN 成绩 >= 150 AND 成绩 < 180 THEN 1
ELSE 0
END
) AS col150to180,
SUM(
CASE
WHEN 成绩 >= 180 AND 成绩 < 210 THEN 1
ELSE 0
END
) AS col180to210
FROM yourTable
GROUP BY 班级
ORDER BY 班级

[davorsuker39]

select 班级,count(*) as 实考人数,
sum(case when 成绩>150 and 成绩<180 then 1 else 0 end) as col150to180,
sum(case when 成绩>180 and 成绩<210 then 1 else 0 end) as col150to180
from t
group by 班级

[不是导演李安]

select 班级,'实考人数' = count(*),
(select (select count(*) from 表 where 成绩 between 150 and 180) aa) col150to180,
(select (select count(*) from 表 where 成绩 between 180 and 210) aa) col180to210
from 表
group by 班级

[pinyu]

谢谢，结题

[qqhbt]

select 班级,count(*)as 实考人数,
　　　sum(case when 成绩>150 and 成绩<180 then 1 else 0 end) as coll50to180,
　　　sum(case when 成绩>180 and 成绩<210 then 1 else 0 end) as cloo180to210
from test group by 班级 order by 班级