|
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 |
