Select the maximum 9 values and sum the rest in Postgresql

SadovniK 0
Viewed 41

There is a table with two columns. I need to sort the table by the "values" column. Leave 9 maximum values. Sum the remaining values and leave them under the name "Other".
Subqueries cannot be used. But I can use window functions.

initial values:

"name"  "value"
"A"        1
"B"        1
"C"        2
"D"        4
"E"        5
"F"        1
"G"        4
"H"        7
"I"        5
"J"        4
"K"        2
"L"        6
"M"        5
"N"        4

result values:

"name"  "value"
  "H"      7
  "L"      6
  "E"      5
  "I"      5
  "M"      5
  "D"      4
  "G"      4
  "J"      4
  "N"      4
 "Other"   7

enter image description here

Postgresql 14

The code for creating a table, as in the example:

create table temp_table
(name varchar(20),
value int);

insert into temp_table values ('A', '1');
insert into temp_table values ('B', '1'); 
insert into temp_table values ('C', '2'); 
insert into temp_table values ('D', '4'); 
insert into temp_table values ('E', '5'); 
insert into temp_table values ('F', '1'); 
insert into temp_table values ('G', '4'); 
insert into temp_table values ('H', '7'); 
insert into temp_table values ('I', '5'); 
insert into temp_table values ('J', '4'); 
insert into temp_table values ('K', '2'); 
insert into temp_table values ('L', '6');
insert into temp_table values ('M', '5'); 
insert into temp_table values ('N', '4');
sql postgresql
1 Answers

Here are two valid solutions that don't involve any kind of "subquery". Hopefully Apache Superset will accept one of these options:

select
    case when row_number() over (order by sum(value) desc, name) > 9
         then 'Other' else name end as name,
    case when row_number() over (order by sum(value) desc, name) > 9
         then sum(sum(value)) over (
                      order by sum(value) desc
                      rows between current row and unbounded following)     
         else sum(value) end as value
from T
group by name
order by row_number() over (order by sum(value) desc, name), value desc
limit 10

or

select distinct on (
    case when row_number() over (order by sum(value) desc, name) > 9
         then 10 else row_number() over (order by sum(value) desc, name) end
)
    case when row_number() over (order by sum(value) desc, name) > 9
         then 'Other' else name end as name,
    case when row_number() over (order by sum(value) desc, name) > 9
         then sum(sum(value)) over (
                      order by sum(value) desc
                      rows between current row and unbounded following)     
         else sum(value) end as value
from T
group by name
order by case when row_number() over (order by sum(value) desc, name) > 9
              then 10 else row_number() over (order by sum(value) desc, name) end,
         value desc

https://dbfiddle.uk/Pi1IxGTT

You should be able to parameterize either of these approaches for a different number of results.

Here's a similar method that handles ties that continue past the 9th-place position:

https://dbfiddle.uk/mI8ZuMYP

shawnt000
Related