Select the country whose sum of population is 80% of total population of whole country-CodePudding

I have a table of 25590 rows and 2 column which has

Country|Population
A|1234
A|2134
B|2342
B|2193

like this there are 30 country and we have to calculate top populated countries which will occupy 80% population of all the country together. for example

population of A population of B  ...... population of N must be 80% of total population of country added together.

For that I tried these method

SELECT distinct
    country,
    population,
    PERCENT_RANK() OVER (
        PARTITION BY country
        ORDER BY population desc
    )
FROM 
    public.age_specific_notification_rate;

From this way neither I could sort the population nor I could calculate sum of each country. Then i tried this way

select distinct country,sum(population) 
from public.age_specific_notification_rate
group by country,population

This way I couldn't get distinct country with total sum of one country.

Method I tried is so far from desired result. Please help me solve it

CodePudding user response：

Try the following:

WITH running_sum AS
  (
    SELECT Country, SUM(tot_pop) OVER (ORDER BY tot_pop DESC) / SUM(tot_pop) OVER () rs
    FROM
     (
       SELECT Country, SUM(Population) tot_pop
       FROM age_specific_notification_rate
       GROUP BY Country
     ) T
  )
SELECT Country, rs FROM running_sum WHERE TRUNC(rs,1) <= 0.8

The inner query finds the sum of the population for each country, and the outer query finds the running sum of the population for the countries ordered by their SUM(Population) from high to low.

SUM(tot_pop) OVER (ORDER BY tot_pop DESC) is used to calculate the running sum of tot_pop for all countries starting from the country with the highest tot_pop.

SUM(tot_pop) OVER () is used to find the total sum of tot_pop for all countries.

By dividing the running sum by the total sum you get the percentage of the population (up to the current row) over the total sum of the population for all countries. Note that we used ORDER BY tot_pop DESC to calculate the running sum and this will guarantee that countries with the highest population are included first.

See a demo.