可以使用两个聚合级别：

select (case when seqnum <= 4 then description else 'Other' end), sum(cnt) as cnt
from (select description, count(*) as cnt,
             row_number() over (order by count(*) desc) as seqnum
      from @t
      group by description
     ) d
group by (case when seqnum <= 4 then description else 'Other' end)
order by min(seqnum);

使用窗口功能 COUNT() :

with 
  cte as (
    select distinct
      description, 
      count(*) over (partition by description) counter
    from @t
  ),
  top3 as (select top 3 with ties * from cte order by counter desc)  
select description, counter
from (
  select description, counter, 1 ord from top3
  union all
  select distinct 'Others', count(*) over (), 2
  from @t
  where description not in (select description from top3)
) t
order by ord, counter desc

请看演示。
结果：

> description | counter
> :---------- | ------:
> Nose        |       6
> Digestive   |       5
> Cardiac     |       4
> Muscular    |       4
> Others      |       3

如果您似乎想处理关系，请使用rank（）而不是row\ u number（）：

select description
, sum(cnt) as cnt
from (
    select case
        when rank() over(order by count(*) desc) <= 3 then description
        else 'Other' end as description
    , count(*) as cnt
    from @t
    group by description
)
group by description