drop table main
create table main(id int identity,team varchar(10))
go
insert into main select 'term1'
GO 3
insert into main select 'term2'
GO 4
insert into main select 'term3'
GO 5

--

select id,team,(select count(1) [count] from main b where a.team=b.team group by team )[count]
from main a
order by [count] desc