+Adding to Scott On Tue, Oct 13, 2015 at 6:25 PM, Scott Mead <sco...@openscg.com> wrote:
> > > > 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=# > > Not sure which PG version you are using, but if you are on 9.4, you may use filters as below. postgres=# SELECT * FROM stats_archive ; cname ------- I I U J K (5 rows) postgres=# WITH total AS ( SELECT COUNT(*) cnt, cname, ROW_NUMBER() OVER() FROM stats_archive GROUP BY 2 ) SELECT 'others' as cname, sum(cnt) filter (where row_number >2) FROM total UNION SELECT cname, cnt FROM total WHERE row_number<=2; cname | sum --------+----- J | 1 I | 2 others | 2 (3 rows) -- > Scott Mead > OpenSCG > www.openscg.com > >> >> >> Alex >> >> > -- Regards, Dinesh manojadinesh.blogspot.com