CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-query
ls_dev
ls_dev

Reputation: 191

Sum column in a multi table hierarchy

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

I made a new post for my next question as my original one was answered correctly and quickly. However, I've been asked not to rely on another question so here's 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 have altered places table so that each 'place' now has a 'number_of_pubs' column, what's the best way to calculate this in a hierarchy?

For example, UK would show 108 pubs, London would show 56 and King's Cross would show 6.

Hopefully this explains what I'm trying to achieve.

My current idea is to pass the 'type' and 'id' to a function which then works out the result by looping through child records and adding up the pubs.

Before I start, is there a nicer way or am I on the right track?

Edit: Since I posted this, I have actually got the function to work but I would be interested to see if it was possible using only SQL. If it isn't I will post my solution, but I will have to rework it as my data isn't the same as my example.

Thanks again.

Upvotes: 4

Views: 200

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You can do something very similar to Sander's solution to the first question; in fact using the same CTE, with the addition of a no_of_pubs column (though some values it gets are currently not used):

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;

That gives:

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

Which has the Kings Cross, London and UK values you wanted

SQL Fiddle demo.

Upvotes: 1

Related Questions