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
Alex