Re: Creating Report for PieChart

> On Oct 13, 2015, at 19:56, Alex Magnum <magnum11200(at)gmail(dot)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.

> Alex