I have two tables PLACE and ADRESS. PLACE includes places like parent-child hierarcy. ADRESS includes adresses with PLACE_ID column. These tables as shown below;
PLACE
ID PARENT_ID NAME CONTINENT
11 null USA America
22 11 New York America
33 22 Manhattan America
44 null Brasil America
55 44 Rio America
66 null France Europe
77 66 Paris Europe
88 66 Nice Europe
MEMBER
ID PLACE_ID NAME ADRESS
1 22 .. ..
2 77 .. ..
3 33 .. ..
4 22 .. ..
5 55 .. ..
6 55 .. ..
7 88 .. ..
8 88 .. ..
9 88 .. ..
10 22 .. ..
Expected Result:
ID PARENT_ID MEMBER_COUNT PLACE_NAME CONTITNET
11 null 4 USA America
22 11 4 New York America
33 22 1 Manhattan America
44 null 2 Brasil America
55 44 2 Rio America
66 null 4 France Europe
77 66 1 Paris Europe
88 66 3 Nice EuropeI want to find out how many members are in which place. I couldn't add child places's member count to parent place. My query as shown below;
WITH MEMBER_COUNT_BY_PLACE AS (
SELECT P.PLACE_ID, COUNT(P.ID) AS MEMBER_COUNT
FROM MEMBER P
GROUP BY P.PLACE_ID
) SELECT C.ID, C.NAME, C.PARENT_ID AS PARENTID, C.CONTINENT, SUM(NVL(D.MEMBER_COUNT, 0)) AS MEMBER_COUNT
FROM PLACE C LEFT JOIN MEMBER_COUNT_BY_PLACE D ON D.PLACE_ID = C.ID
START WITH D.PLACE_ID IS NOT NULL
CONNECT BY PRIOR C.PARENT_ID = C.ID
GROUP BY C.ID, C.NAME, C.PARENT_ID, C.CONTINENT
ORDER BY CONTINENT ASC, PARENT_ID ASC NULLS FIRST;
Thank you for your help.
This query also aggregates child members:
with mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
select
p.id, p.parent_id, p.name, p.continent
, 0 as child_members
, (select count(*) from member m where m.place_id=p.id) as own_members
, row_number()over(partition by p.parent_id order by p.id) ff
, sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
from place p
where not exists(select null from place child where p.id = child.parent_id)
union all
select p.id, p.parent_id, p.name, p.continent
, mcnt.level_members + mcnt.child_members as child_members
, (select count(*) from member m where m.place_id=p.id) as own_members
, row_number()over(partition by p.parent_id order by p.id) ff
, sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
from mcnt, place p
where mcnt.parent_id=p.id
and mcnt.ff=1
)
select
id, parent_id, name, continent, child_members, own_members
,child_members+own_members as total_cnt
from mcnt
order by id;
Full example:
with
-- sample data
place (id, parent_id, name, continent) as
(select 11, null, 'USA' , 'America' from dual union all
select 22, 11 , 'New York' , 'America' from dual union all
select 33, 22 , 'Manhattan', 'America' from dual union all
--
select 35, 33 , 'Central Park', 'America' from dual union all
select 44, null, 'Brasil' , 'America' from dual union all
select 55, 44 , 'Rio' , 'America' from dual union all
select 66, null, 'France' , 'Europe' from dual union all
select 77, 66 , 'Paris' , 'Europe' from dual union all
select 88, 66 , 'Nice' , 'Europe' from dual
),
member (id, place_id) as
(select 1, 22 from dual union all
select 2, 77 from dual union all
select 3, 33 from dual union all
select 4, 22 from dual union all
select 5, 55 from dual union all
select 6, 55 from dual union all
select 7, 88 from dual union all
select 8, 88 from dual union all
select 9, 88 from dual union all
select 10, 22 from dual
--
union all select 1001, 35 from dual
union all select 1002, 35 from dual
union all select 1003, 35 from dual
),
mcnt (id, parent_id, name, continent, child_members, own_members, ff,level_members) as (
select
p.id, p.parent_id, p.name, p.continent
, 0 as child_members
, (select count(*) from member m where m.place_id=p.id) as own_members
, row_number()over(partition by p.parent_id order by p.id) ff
, sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
from place p
where not exists(select null from place child where p.id = child.parent_id)
union all
select p.id, p.parent_id, p.name, p.continent
, mcnt.level_members + mcnt.child_members as child_members
, (select count(*) from member m where m.place_id=p.id) as own_members
, row_number()over(partition by p.parent_id order by p.id) ff
, sum((select count(*) from member m where m.place_id=p.id))over(partition by p.parent_id) level_members
from mcnt, place p
where mcnt.parent_id=p.id
and mcnt.ff=1
)
select
id, parent_id, name, continent, child_members, own_members
,child_members+own_members as total_cnt
from mcnt
order by id;Results:
ID PARENT_ID NAME CONTINE CHILD_MEMBERS OWN_MEMBERS TOTAL_CNT
---------- ---------- ------------ ------- ------------- ----------- ----------
11 USA America 7 0 7
22 11 New York America 4 3 7
33 22 Manhattan America 3 1 4
35 33 Central Park America 0 3 3
44 Brasil America 2 0 2
55 44 Rio America 0 2 2
66 France Europe 4 0 4
77 66 Paris Europe 0 1 1
88 66 Nice Europe 0 3 3
9 rows selected.