Re: [GENERAL] Creating Report for PieChart

[Bruce Momjian]

On Wed, Oct 14, 2015 at 01:56:11AM +0200, Alex Magnum 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?

I think you want the HAVING clause, e.g. HAVING COUNT(*) > 8.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Creating Report for PieChart

[Scott Mead]

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

Re: [GENERAL] Creating Report for PieChart

[Scott Mead]

On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead  wrote:

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

Re: [GENERAL] Creating Report for PieChart

[dinesh kumar]

+Adding to Scott

On Tue, Oct 13, 2015 at 6:25 PM, Scott Mead  wrote:

>
>
>
> On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead  wrote:
>
>>
>>
>> On Oct 13, 2015, at 19:56, Alex Magnum  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