这个连接查询怎么写

[yangguo_god]

表a
id bid name
1 1 dd
2 1 df
3 2 cc
表b
id name
1 ee
2 rr
通过a.bid=b.id关联
我想通过bid对表a进行分组统计,语句这么写得
select Count(*) as count, bid from a Group By bid where bid>0";
结果为
count bid
2 1 ->怎么显示ee?
1 2 ->怎么显示rr?
查询语句怎么写?

[yangguo_god]

在sqlserver中是可以用,在access中就不行了,access该怎么写

[victorycyz]

Access:

select bid,b.name,count(bid) as [count]
from a inner join b on a.bid=b.id
where bid>0
group by bid,b.name

[leeboyan]

select a.count,a.bid,b.name from (select Count(*) as count, bid from a Group By bid where bid>0)a,tableb b where a.bid=b.id

[victorycyz]

select bid,b.name,count(*) as [count]
from a join b on a.bid=b.id
where bid>0
group by bid,b.name