How to select first 5 records and group rest others records in sql?-CodePudding

Suppose I have 2 columns NAME and COUNT.

NAME	COUNT
a1	2
a2	4
a3	5
a4	1
a5	6
a6	2
a7	4
a8	6
a9	7
a10	4
a11	1

I want to select first 5 records and group the rest others as one record( naming that record as others)

The output I need is

NAME	COUNT
a1	2
a2	4
a3	5
a4	1
a5	6
others	24

In others I need sum of all the count values excluding first 5 records.

CodePudding user response：

We can use a union approach with the help of ROW_NUMBER():

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY NAME) rn
    FROM yourTable t
)

SELECT NAME, COUNT
FROM
(
    SELECT NAME, COUNT, 1 AS pos FROM cte WHERE rn <= 5
    UNION ALL
    SELECT 'others', SUM(COUNT), 2 FROM cte WHERE rn > 5
) t
ORDER BY pos, NAME;