Query "top 10 and others"

From: Edson Richter <edsonrichter(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Query "top 10 and others"
Date: 2014-07-04 20:32:49
Message-ID: BLU182-W208737ECEAA90117062784CF000@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-07-04 22:19:51 Re: Query "top 10 and others"
Previous Message Bosco Rama 2014-07-04 18:55:19 Re: pg_dump slower than pg_restore