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.