How do i sum values after top 3 rows and group them under others

Question

I have following table of participants of an apple picking completion. I have a table where all the participants are visible with the number of apples they have picked.

Now i want a table with that shows only the top 3 and the rest will be grouped under 'Others' and the total apples picked should appear against other

I have created the table which will have all the ids and the total apples collected

declare  @t table
    (
        id int,
        Apples_picked int
    )

    insert into @t
    select 1,10
    union
    select 2,12
    union
    select 3,3
    union
    select 4,15
    union
    select 5,23

Required output for the above table

ID  Name    Apples picked
5   winner  23
4   2nd     15
2   3rd     12
    Others  13

I am not sure how to add the everything after 3rd and sum it any guidance is much appreciated

Answer 1

;WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Apples_picked DESC) rn
    FROM @t
)
SELECT ID, 
    CASE rn WHEN 1 THEN 'Winner' WHEN 2 THEN '2nd' WHEN 3 THEN '3rd' END AS Name, 
    cte.Apples_picked
FROM cte 
WHERE rn <= 3
UNION ALL 
SELECT NULL, 'Others', SUM(apples_picked)
FROM cte
WHERE rn > 3

Returns:

ID      Name    Apples_picked
5       Winner  23
4       2nd     15
2       3rd     12
NULL    Others  13

Answer 2

You want window function :

select max(case when Name <> 'Others' then id end) id,  Name, sum(Apples_picked) as [Apples picked]
from (select t.*, 
             dense_rank() over (order by Apples_picked desc) as seq
      from @t t
     ) t cross apply
     ( values (case seq when 1 then 'winner' when 2 then '2nd' when 3 then '3rd' else 'Others' end) 
     ) tt(Name)
where seq <= 33
group by Name
order by id desc;

If the order by is really important then you could change :

ORDER BY (CASE WHEN Name = 'Others' THEN 1 ELSE 0 END), [Apples picked] DESC

Here is a Demo.