group by 与count 的问题 ,求一SQL
create   table   tb(id   int   ,class   varchar)--class种类就只有三种
insert   tb  
select   1, 'a '   union   all
select   1, 'a '   union   all
select   1, 'b '   union   all
select   1, 'c '   union   all
select   2, 'a '   union   all
select   2, 'b '   union   all
select   2, 'b '  
select   *   from   tb

想查找出按id分组得到的   a     ,b     ,c   的数量
    如下
id       a       b         c
1       2       1           1
2       1       2         0  
求一SQL

------解决方案--------------------
select
id,
a=sum(case class when 'a ' then 1 else 0 end),
b=sum(case class when 'b ' then 1 else 0 end),
c=sum(case class when 'c ' then 1 else 0 end)
from
tb
group by
id
------解决方案--------------------
select id,
sum(case when class= 'a ' then 1 else 0 end) a,
sum(case when class= 'b ' then 1 else 0 end) b,
sum(case when class= 'c ' then 1 else 0 end) c
from tb
group by id
order by id
------解决方案--------------------
select id,class,count(*) from tb group by class,id