CODEWITHSUNDEEP
sqlsql-serverdatabaseoracle-database
Fakhar uz zaman
Fakhar uz zaman

Reputation: 341

Sum hierarchical values in table for Continent, countries and states

Cosider following structure for population.

Asia(100+50+150=300)
    > China (20+80=100)
        >   China_StateA (20)
        >   China_StateB (80)
    > KSA (10+40=50)
        >   KSA_StateA (10)
        >   KSA_StateB (40) 
    > India (70+80=150)
        >   India_StateA (70)
        >   India_StateB (80)   


Europe(50+15=65)
    > England (50)
        >   England_StateA (20)
        >   England_StateB (30)
    > Ireland (5+10=15)
        >   Ireland_StateA (5)
        >   Ireland_StateB (10)

If i have same structure in a single database table like following

Id  Type    Name    ParentId    Population
1   Continent   Asia    0   
2   Country China   1   
3   State   China_StateA    2   20
4   State   China_StateB    2   80
5   Country KSA 1   
6   State   KSA_StateA  5   10
7   State   KSA_StateB  5   40
8   Country India   1   
9   State   India_StateA    8   70
10  State   India_StateB    8   80
11  Continent   Europe  0   
12  Country England 11  
13  State   England_StateA  12  20
14  State   England_StateB  12  30
15  Country Ireland 11  
16  State   Ireland_StateA  15  5
17  State   Ireland_StateB  15  10

Then how can i write a sql query that will return population on each level. i.e. will sum up population of states to find population of country. Similarly will sum up population of countries to find population of continents.

Upvotes: 1

Views: 655

Answers (3)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

You can do it using CTE

WITH CTE_TMP
AS
(
    SELECT  Id,[Type],Name,ParentId,[Population]
    FROM    MyTable
    WHERE   [Type]  =   'State'
    UNION ALL
    SELECT  M.Id,M.[Type],M.Name,M.ParentId,ISNULL(C.[Population],0)
    FROM    MyTable M
    INNER JOIN CTE_TMP C ON M.Id = C.ParentId
)
SELECT Id,[Type],Name,ParentId,SUM([Population]) AS [Population] 
FROM CTE_TMP 
GROUP BY Id,[Type],Name,ParentId
ORDER BY id

Upvotes: 1

Rumpelstinsk
Rumpelstinsk

Reputation: 3241

Kordi's solution will help you, if you want to get the data for a countries and continents in separate views or specific countries. But if you want the see all in a single view, and you only have 2 levels in your herarchy, you can use this query.

SELECT parent.Id
    parent.Name,
    parent.ParentId
    SUM(childs.Population)
FROM mytable parent
    INNER JOIN mytable childs ON parent.Id = childs.ParentId
WHERE parent.Type = 'Continent'
GroupBy parent.Id, parent.Name, parent.ParentId

UNION

SELECT parent.Id
    parent.Name,
    parent.ParentId
    SUM(childs.Population)
FROM mytable parent
    INNER JOIN mytable childs ON parent.Id = childs.ParentId
WHERE parent.Type = 'Country'
GroupBy parent.Id, parent.Name, parent.ParentId

NOTE: If you have more than 2 hierarchy levels, 2 workarounds to this query are, using XML PATH or cursors inside a store procedure or function.

Upvotes: 0

Kordi
Kordi

Reputation: 2465

If you want to sum after continents, one approach is just to add a column continent

Sum after continent is then

SELECT sum(Population) FROM yourTable GROUP BY continent.

Another approach is something like this with a hardcoded list

Sum after continents

Just use sum and group by with REGEXP_SUBSTR with in and a hardcoded List. In my Case Germany,England should represent Europe.

SELECT sum(Population) FROM yourTable GROUP BY REGEXP_SUBSTR('Name','(.*)_?') IN ('England', 'Germany')

Upvotes: 1

Related Questions