Обсуждение: 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
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 <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
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 | Malaysia65 | Singapore57 | Thailand26 | Indonesia15 | France14 | United States14 | India13 | Philippines12 | Vietnam10 | Republic of Korea10 | Canada7 | Australia6 | Brazil6 | Czech Republic5 | Switzerland4 | Saudi Arabia3 | Ireland3 | Japan3 | Sweden3 | South Africa3 | Belarus3 | Colombia3 | United Kingdom1 | Perucountry_name | count | perc-----------------+-------+-------Malaysia | 302 | 51.4Singapore | 65 | 11.0Thailand | 57 | 9.7Indonesia | 26 | 4.4France | 15 | 2.6United States | 14 | 2.4India | 14 | 2.4Others | 95 | 16.1Total | 588 | 100Thanks a lot for any suggestions
I would use rank to get a rank number for each record.
Alex
On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead <scottm@openscg.com> wrote:
Use 'rank' to generate the rank order of the entry.
There's probably an easier way to do this without a sub-select, but, it works.
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 | Malaysia65 | Singapore57 | Thailand26 | Indonesia15 | France14 | United States14 | India13 | Philippines12 | Vietnam10 | Republic of Korea10 | Canada7 | Australia6 | Brazil6 | Czech Republic5 | Switzerland4 | Saudi Arabia3 | Ireland3 | Japan3 | Sweden3 | South Africa3 | Belarus3 | Colombia3 | United Kingdom1 | Perucountry_name | count | perc-----------------+-------+-------Malaysia | 302 | 51.4Singapore | 65 | 11.0Thailand | 57 | 9.7Indonesia | 26 | 4.4France | 15 | 2.6United States | 14 | 2.4India | 14 | 2.4Others | 95 | 16.1Total | 588 | 100Thanks a lot for any suggestionsI would use rank to get a rank number for each record.
Sorry, Sent the last one from my phone, here's an example:
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)
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
+Adding to Scott
--
On Tue, Oct 13, 2015 at 6:25 PM, Scott Mead <scottm@openscg.com> wrote:
On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead <scottm@openscg.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 | Malaysia65 | Singapore57 | Thailand26 | Indonesia15 | France14 | United States14 | India13 | Philippines12 | Vietnam10 | Republic of Korea10 | Canada7 | Australia6 | Brazil6 | Czech Republic5 | Switzerland4 | Saudi Arabia3 | Ireland3 | Japan3 | Sweden3 | South Africa3 | Belarus3 | Colombia3 | United Kingdom1 | Perucountry_name | count | perc-----------------+-------+-------Malaysia | 302 | 51.4Singapore | 65 | 11.0Thailand | 57 | 9.7Indonesia | 26 | 4.4France | 15 | 2.6United States | 14 | 2.4India | 14 | 2.4Others | 95 | 16.1Total | 588 | 100Thanks a lot for any suggestionsI 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 | 1CA | 15 | 2SP | 8 | 3IT | 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, rankFROM (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 | 1CA | 15 | 2SP | 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)