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

user_8288019 0
Viewed 101

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

sql-server sql
4 Answers
;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
user_12080340

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.

user_55294450

Just another option using CHOOSE()

Example

;WITH cte AS (
    Select *
          ,[Name] = IsNull(choose(ROW_NUMBER() OVER (ORDER BY Apples_picked DESC),'Winner','2nd','3rd'),'Others')
    FROM @t
)
Select ID = max(case when [Name]='Others' then null else ID end)
      ,[Name]
      ,Apples_picked=sum(Apples_picked)
 From cte
 Group By Name
 Order By case when [Name]='Others' then 1 else 0 end
         ,sum(Apples_picked) Desc

Returns

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

I'd do that with two #Temp tables. Select the top 3 into the first; select the sum of all the rows whose ID's are not in the first into the second; return the union of the two #Temp tables.

You could use CTE's rather than #Temp tables - the logic would be the same.

user_60202880
Related