Re: Creating Report for PieChart

On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead <scottm(at)openscg(dot)com> wrote:

>
>
> 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.
>

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
>
>