How retrieve all parent and child rows population in Oracle sql?

I have a table “TB_Population” with some records about the population from all over the world. at this time I want to calculate each title’s population in particular row and demonstrate each level in that table.

I have this table with the following data:

ID     TITLE        PARENT_ID    POPULATION

1      WORLD                         10    
2      AFRICA         1              5  
3      ASIA           1              10
4      EUROPE         1              4
5      GERMANY        4              6
6      FRANCE         4              10
7      ITALY          4              4
8      JAPAN          3              6
9      MORROCO        2              1
10     SPAIN          4              9
11     INDIA          3              8  
12     PORTUGAL       4              2
13     USA            14             10         
14     AMERICA        1              10             
15     NEWYORK        13             5

The expected output table should be as below

   ID     TITLE     POPULATION       LEVEL

    1      WORLD        100             1   
    2      AFRICA       6               2
    3      ASIA         24              2
    4      EUROPE       35              2
    5      GERMANY      6               3
    6      FRANCE       10              3
    7      ITALY        4               3
    8      JAPAN        6               3
    9      MORROCO      1               3   
    10     SPAIN        9               3
    11     INDIA        8               3
    12     PORTUGAL     2               3
    13     USA          15              3
    14     AMERICA      25              2
    15     NEWYORK      5               4

Thanks and best regards

Answer

The tricky part which I see here is you want the LEVEL of title from “BOTTOM TO TOP” and POPULATION from “TOP TO BOTTOM”. For example, AMERICA’s level has to be 2 which means the LEVEL has to be measured from AMERICA -> WORLD, but AMERICA’s population has to be 25 which is the sum of population measured from AMERICA -> NEWYORK. So, I tried this:

SELECT TOP_TO_BOTTOM.TITLE_ALIAS, TOP_TO_BOTTOM.TOTAL_POPULATION, BOTTOM_TO_TOP.MAX_LEVEL FROM
(SELECT TITLE_ALIAS, SUM(POPULATION) AS "TOTAL_POPULATION" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", POPULATION
      FROM TB_POPULATION 
      CONNECT BY PRIOR ID = PARENT_ID) 
GROUP BY TITLE_ALIAS) "TOP_TO_BOTTOM"
INNER JOIN
(SELECT TITLE_ALIAS, MAX(LEV) AS "MAX_LEVEL" FROM
(SELECT CONNECT_BY_ROOT TITLE AS "TITLE_ALIAS", LEVEL AS "LEV"
      FROM TB_POPULATION 
      CONNECT BY PRIOR PARENT_ID = ID) 
GROUP BY TITLE_ALIAS) "BOTTOM_TO_TOP"
ON
BOTTOM_TO_TOP.TITLE_ALIAS = TOP_TO_BOTTOM.TITLE_ALIAS
ORDER BY BOTTOM_TO_TOP.MAX_LEVEL;

You can have a look at the simulation here: https://rextester.com/HFTIH47397. Hope this helps you