Aggregation Sum in SQL (Joins)

Question

I'm having some trouble with summing some column in my query with aggregation. It's a bit difficult to describe what is happening but I'll try my best:
I have 3 tables - details, extra details and places. Places is a table that contains places in the world. Details contains details about events that happened, and extra details provides some more data on the events.
Each place has an ID and a ParentID (Like New York has an ID and it's parent ID is the US. Something like that). The ID of the event(details) appears a number of times as a column in the extra details table. The extra details table also holds the ID of the place that that event occurred at.
OK after all of that, what I'm trying to achieve is, for each place, the sum of the events that happened there. I know it sounds very specific, but it's what the client asked.
Anyhow, example of what I'm trying to get to: NewYork 60, Chicago 20, Houston 10 Then the US will have 90. And it has several levels.
So this is what I was trying to do:

    With C(ID, NAME, COUNTT, ROOT_ID) as 
    (
        SELECT d.ID, d.NAME,
          (SELECT COUNT(LX.ID) as COUNTT 
           FROM EXTRA LX
             RIGHT JOIN d ON LX.PLACE_ID = d.ID -- ****
           GROUP BY d.ID, d.NAME),
           d.ID as ROOT_ID
        FROM PLACES d
        UNION ALL
        SELECT d.ID, d.NAME,
          (SELECT COUNT(LX.ID) as COUNTT 
           FROM EXTRA LX
           RIGHT JOIN d ON LX.PLACE_ID = d.ID
        GROUP BY d.ID, d.NAME),
        C.ROOT_ID
        FROM PLACES dx
          INNER JOIN C ON dx.PARENT_ID = C.ID
     )
     SELECT p.ID, p.NAME, S.SumIncludingChildren
     FROM places p
       INNER JOIN (
         SELECT ROOT_ID, SUM(COUNTT) as SumIncludingChildren
         FROM C
         GROUP BY ROOT_ID
       ) S
       ON p.ID = S.ROOT_ID
     ORDER BY p.ID;

The details table is only for showing their data. I'll add that later. It's only comparing the respective columns. To making it work I don't need that. Only for the site data.
It doesn't work because it doesn't recognizes the 'd' where the '****' is. If I'll put a 'new instance' of that table, it won't work either. So I tried to replicate what the right join by doing 'NOT EXISTS IN' on a query that gets all the places instead of the right join...on. Same problem.
Maybe I don't get something. But I'm really seeking a solution and some explanation. I know my code isn't perfect. Thanks in advance.

EDIT: I'm using OracleSQL on Toad 10.6

Answer 1

create table p(id number, up number, name varchar2(100)); 
create table e(id number, pid number, dsc varchar2(100));

insert into p values (1, null, 'country');
insert into p values (2, 1,    'center');
insert into p values (3, 1,    'province');
insert into p values (4, 2,    'capital');
insert into p values (5, 2,    'suburb');
insert into p values (6, 3,    'forest');
insert into p values (7, 3,    'village');
insert into p values (8, 7,    'shed');
insert into p values (9, 2,    'gov');

insert into e values (1, 8, 'moo');
insert into e values (2, 8, 'clank');
insert into e values (3, 7, 'sowing');
insert into e values (4, 6, 'shot');
insert into e values (5, 6, 'felling');
insert into e values (6, 5, 'train');
insert into e values (7, 5, 'cottage');
insert into e values (8, 5, 'rest');
insert into e values (9, 4, 'president');
insert into e values (10,1, 'GNP');
commit;

with 
  places as
   (select id,
           up,
           connect_by_root id as root,
           level lvl
      from p
    connect by prior id = up),
  ev_stats as
   (select root as place, max(lvl) as max_lvl, count(e.id) as ev_count
      from places left outer join e
        on places.id = e.pid
     group by root)
select max_lvl, p.name, ev_count
  from ev_stats inner join p on p.id = ev_stats.place
 order by max_lvl desc;