Postgres Pro
Downloads
Home   >   mailing lists

Re: Creating Report for PieChart - Mailing list pgsql-general

From Scott Mead
Subject Re: Creating Report for PieChart
Date
Msg-id CAKq0gvLse4dY1WNMxVZjf_xYWKNkdhJsETVkF+Rw8AUAPmkibg@mail.gmail.com
Whole thread Raw
In response to Re: Creating Report for PieChart  (Scott Mead <scottm@openscg.com>)
Responses Re: Creating Report for PieChart  (dinesh kumar <dineshkumar02@gmail.com>)
List pgsql-general
Tree view



On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead <scottm@openscg.com> wrote:


On Oct 13, 2015, at 19:56, Alex Magnum <magnum11200@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=# 

--
Scott Mead
OpenSCG


Alex

pgsql-general by date:

Previous
From: Scott Mead
Date:
Subject: Re: Creating Report for PieChart
Next
From: dinesh kumar
Date:
Subject: Re: Creating Report for PieChart