Hello again,
I would like to ask you again another question that is very close to the one I asked a few days agao (cumulative sum). The solutions you suggested were very interesting… Now that I try to apply it in my scenario, I would like to see how it can be adapted to have also groups count.
Imagine we have this sample data (more or less the same as previous one):
with data as
(
-- one root node
select 1 AS id, NULL as pid, 1 AS t FROM dual UNION ALL
-- one root node with 2 node
select 2 AS id, NULL as pid, 1 AS t FROM dual UNION ALL
select 3 AS id, 2 as pid, 2 AS t FROM dual UNION ALL
select 4 AS id, 2 as pid, 3 AS t FROM dual UNION ALL
select 5 AS id, 2 as pid, 3 AS t FROM dual
)
SELECT id, pid,
FROM data;
On each node, I have an id, a parent id that points to the hierarchical parent and a type (here is t).
I would like to count per node how many types are in descending levels.
In this case, if I start at level 4, I should have a result like this one:
ID PID LEVEL T COUNT(*)
---------- ---------- ---------- ---------- -----------
4 2 1 3 1
2 2 1 1
2 2 2 1
2 2 3 2
Any direction on how to improve the previous query would be much appreciated.