Re: Creating Report for PieChart

+Adding to Scott

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

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

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