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.
>Solution :
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;