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