Thread: Query "top 10 and others"
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
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
Edson Richter wrote > 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? This is the best solution; though it may be faster to calculate all the sums in the CTE then limit 10 the first union part and sum the remaining sums in the second part. That way you do not have to scan the entire places table twice. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Query-top-10-and-others-tp5810597p5810601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.