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

Abhishek Bansal 0
Viewed 49

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.

greatest-n-per-group sql postgresql
1 Answers

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;
Tim Biegeleisen0
Related