Sum all the rows excluding top 2-CodePudding

I have a table like:

I need to group these rows into 3 rows only, where first two rows in resultset will be top two values and rest of the rows need to be combined into one with sum(values). Result would look like-

Code	Value
17	1
19	0
Rest	0

I tried row number with partition, SUM(value) with OVER and some conventional step by step approach which is lots of inefficient code. Is there a way to do that in SQL Server?

CodePudding user response：

If I get it right, you can first enumerate using row_number()

SELECT code, value, ROW_NUMBER() OVER (ORDER BY value DESC) AS rn
FROM t

Now you can use rn in a CASE expression

SELECT CASE WHEN rn < 3 THEN CAST(code AS VARCHAR(10)) ELSE 'Rest' END
     , SUM(VALUE)
FROM (
    SELECT code, value, ROW_NUMBER() OVER (ORDER BY value DESC) AS rn
    FROM t
) AS tmp
GROUP BY CASE WHEN rn < 3 THEN CAST(code AS VARCHAR(10)) ELSE 'Rest' END

This is of course non-deterministic, but according to your comment that does not matter