Re: [GENERAL] Creating Report for PieChart
+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
Re: [GENERAL] Creating Report for PieChart
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
> 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
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
[GENERAL] Creating Report for PieChart
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 Alex
