Sum of a column in a hierarchy of several tables

Question

A few hours ago I asked this question about hierarchy over two tables.

I made a new message for my next question, as my original answer was given correctly and quickly. However, I was asked not to rely on another question, so here is the scenario:

I have the following tables:

    create table areas
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  parent_id     NUMBER
);

-- Top Level
Insert into areas (id, name)
 Values (1, 'Europe');
Insert into areas (id, name)
 Values (2, 'Americas');
Insert into areas (id, name)
 Values (3, 'Asia ex Japan');
Insert into areas (id, name)
 Values (4, 'Japan');

 -- Jurisdictions
Insert into areas (id, name, parent_id)
 Values (5, 'UK', 1);
Insert into areas (id, name, parent_id)
 Values (7, 'France', 1);
Insert into areas (id, name, parent_id)
 Values (6, 'Germany', 1);
Insert into areas (id, name, parent_id)
 Values (8, 'Italy', 1);
Insert into areas (id, name, parent_id)
 Values (9, 'US', 2);
Insert into areas (id, name, parent_id)
 Values (10, 'Australia', 3);
Insert into areas (id, name, parent_id)
 Values (11, 'New Zealand', 3);

create table places
(
  id            NUMBER not null,
  name          VARCHAR2(200) not null,
  area_id       NUMBER,
  parent_id     NUMBER,
  no_of_pubs    NUMBER
);

Insert into places (id, name, area_id, parent_id)
 Values (1, 'London', 5, NULL, 50);
Insert into places (id, name, area_id, parent_id)
 Values (2, 'Bath', 5, NULL, 20);
Insert into places (id, name, area_id, parent_id)
 Values (3, 'Liverpool', 5, NULL, 32);
Insert into places (id, name, area_id, parent_id)
 Values (4, 'Paris', 7, NULL, 64);
Insert into places (id, name, area_id, parent_id)
 Values (5, 'New York', 9, NULL, 76);
Insert into places (id, name, area_id, parent_id)
 Values (6, 'Chicago', 9, NULL, 5);
Insert into places (id, name, area_id, parent_id)
 Values (7, 'Kings Cross', 5, 1, 6);
Insert into places (id, name, area_id, parent_id)
 Values (8, 'Tower of London', 5, 1, 0);

And the following code to represent the data:

with src as (
  select 'A' type, a.id, a.name, a.parent_id, null area_id from areas a
  union all
  select 'P', -p.id id, p.name, -p.parent_id parent_id, area_id from places p)
select 
  src.*, level
from 
  src
start with 
  type = 'A' and parent_id is null
connect by 
  parent_id = prior id or 
  parent_id is null and area_id = prior id

Since the last post, I changed the places table, so that every place now has a column called number_of_pubs, what is the best way to calculate this in a hierarchy?

For example, Great Britain will show 108 pubs, London will show 56, and King Cross will show 6.

Hope this explains what I'm trying to achieve.

- "" "id" , , .

, ?

: , , , , SQL. , , , .

.

Answer 1

- Sander ; CTE, no_of_pubs ( , , ):

with src as (
  select 'A' type, a.id, a.name, a.parent_id, null area_id, 0 no_of_pubs
  from areas a
  union all
  select 'P', -p.id id, p.name, -p.parent_id parent_id, area_id, no_of_pubs
  from places p
)
select s1.name, s1.no_of_pubs, (
    select sum(s2.no_of_pubs)
    from src s2
    start with s2.id = s1.id
    connect by s2.parent_id = prior s2.id
    or (s2.parent_id is null and s2.area_id = prior s2.id)
  ) as rollup_no_of_pubs
from src s1;

:

NAME                           NO_OF_PUBS ROLLUP_NO_OF_PUBS
------------------------------ ---------- -----------------
Europe                                  0               172
Americas                                0                81
Asia ex Japan                           0                 0
Japan                                   0                 0
UK                                      0               108
France                                  0                64
Germany                                 0                 0
Italy                                   0                 0
US                                      0                81
Australia                               0                 0
New Zealand                             0                 0
London                                 50                56
Bath                                   20                20
Liverpool                              32                32
Paris                                  64                64
New York                               76                76
Chicago                                 5                 5
Kings Cross                             6                 6
Tower of London                         0                 0

Kings Cross, London UK,

- SQL Fiddle.