Re : Query "top 10 and others"

<span style="font-size:10pt;"><p style="margin-top:0;margin-bottom:0;">Would you please provide an example, even hypothetical?&nbsp;</p><p style="margin-top:0;margin-bottom:0;">&nbsp;</p><dev3_jjy>Atenciosamente,<br><br>Edson Richter&nbsp;</dev3_jjy><br><br>------ Mensagem original ------<br><b>De:&nbsp;</b>David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com><br><b>Data:&nbsp;</b>04/07/2014 19h22<br><b>Para:&nbsp;</b>pgsql-general(at)postgresql(dot)org;<br><b>Assunto:</b>Re: [GENERAL] Query &quot;top 10 and others&quot;<br><br><pre>Edson Richter wrote
&gt; I would like to construct a query, to be used in graphics (Pie Chart, to
&gt; be more precise), and to avoid having 500 slices, I would like to make a
&gt; query that returns the top ten, and then all the rest summed.
&gt;
&gt; I know I can do it by using some repetition, like:
&gt;
&gt; a) Assume &quot;places&quot; table with population with structure
&gt;
&gt; create table places (
&gt; &nbsp;&nbsp;id as integer primary key,
&gt; &nbsp;&nbsp;country as varchar(3),
&gt; &nbsp;&nbsp;state as varchar(50),
&gt; &nbsp;&nbsp;city as varchar(50),
&gt; &nbsp;&nbsp;population integer
&gt; )
&gt;
&gt; b) There are not so many records in table (my country have ~5500 cities,
&gt; and 27 states), and initially, there will be only 1 country.
&gt;
&gt; with QRY as (select C1.country, C1.state, sum(C1.population)
&gt; &nbsp;&nbsp;from places C1
&gt; &nbsp;&nbsp;group by 1, 2
&gt; &nbsp;&nbsp;&nbsp;order by 3 DESC
&gt; &nbsp;&nbsp;limit 10)
&gt;
&gt; select * from QRY
&gt; union
&gt; select 'others' as &quot;country&quot;, '' as &quot;state&quot;, sum(population)
&gt; &nbsp;&nbsp;from places
&gt; &nbsp;where not exists (select 1 from QRY where country = QRY.country and state
&gt; = QRY.state)
&gt;
&gt;
&gt; Can I simplify the query by using some sort of window function or other
&gt; 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. &nbsp;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.

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

</pre></span>