I would like to construct a query, to be used in graphics (Pie Chart, to be more precise), and to avoid having 500 slices, I would like to make a query that returns the top ten, and then all the rest summed.
I know I can do it by using some repetition, like:
a) Assume "places" table with population with structure
create table places (
id as integer primary key,
country as varchar(3),
state as varchar(50),
city as varchar(50),
population integer
)
b) There are not so many records in table (my country have ~5500 cities, and 27 states), and initially, there will be only 1 country.
with QRY as (select C1.country, C1.state, sum(C1.population)
from places C1
group by 1, 2
order by 3 DESC
limit 10)
select * from QRY
union
select 'others' as "country", '' as "state", sum(population)
from places
where not exists (select 1 from QRY where country = QRY.country and state = QRY.state)
Can I simplify the query by using some sort of window function or other PostgreSQL feature I don't know yet?
Thanks,
Edson Richter