On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead <sco...@openscg.com> wrote:
> > > On Oct 13, 2015, at 19:56, Alex Magnum <magnum11...@gmail.com> wrote: > > Hello, > I need to process some statistics for a pie chart (json) where I only want > to show a max of 8 slices. If I have more data points like in below table I > need to combine all to a slice called others. If there are less or equal 8 > i use them as is. > > I am currently doing this with a plperl function which works well but was > just wondering out of curiosity if that could be done withing an sql query. > > Anyone having done something similar who could point me in the right > direction? > > > SELECT count(*),country_name FROM stats_archive WHERE id=400 GROUP BY > country_name ORDER BY COUNT DESC; > count | country_name > -------+------------------- > 302 | Malaysia > 65 | Singapore > 57 | Thailand > 26 | Indonesia > 15 | France > 14 | United States > 14 | India > 13 | Philippines > 12 | Vietnam > 10 | Republic of Korea > 10 | Canada > 7 | Australia > 6 | Brazil > 6 | Czech Republic > 5 | Switzerland > 4 | Saudi Arabia > 3 | Ireland > 3 | Japan > 3 | Sweden > 3 | South Africa > 3 | Belarus > 3 | Colombia > 3 | United Kingdom > 1 | Peru > > > country_name | count | perc > -----------------+-------+------- > Malaysia | 302 | 51.4 > Singapore | 65 | 11.0 > Thailand | 57 | 9.7 > Indonesia | 26 | 4.4 > France | 15 | 2.6 > United States | 14 | 2.4 > India | 14 | 2.4 > Others | 95 | 16.1 > Total | 588 | 100 > > Thanks a lot for any suggestions > > I would use rank to get a rank number for each record. > Sorry, Sent the last one from my phone, here's an example: Use 'rank' to generate the rank order of the entry. postgres=# select country, count(1) num_entries, rank() over (order by count(1) DESC) from test GROUP by country ORDER BY num_entries DESC; country | num_entries | rank ---------+-------------+------ US | 20 | 1 CA | 15 | 2 SP | 8 | 3 IT | 7 | 4 (4 rows) There's probably an easier way to do this without a sub-select, but, it works. postgres=# SELECT country, num_entries, rank FROM (select country, count(1) num_entries, rank() over (order by count(1) DESC) FROM test GROUP by country ) foo WHERE rank < 4; country | num_entries | rank ---------+-------------+------ US | 20 | 1 CA | 15 | 2 SP | 8 | 3 (3 rows) postgres=# -- Scott Mead OpenSCG www.openscg.com > > > Alex > >