By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
id NUMBER(10,0) PRIMARY KEY,
parent_id REFERENCES table_name (id),
count NUMBER(10,0)
)
INSERT INTO table_name (id, parent_id, count)
SELECT 1, NULL, 1 FROM DUAL UNION ALL
SELECT 2, 1, 2 FROM DUAL UNION ALL
SELECT 3, 1, 3 FROM DUAL UNION ALL
SELECT 4, 2, 4 FROM DUAL UNION ALL
SELECT 5, 3, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;
6 rows affected
SELECT id, parent_id, count,
( SELECT ROUND(EXP(SUM(LN(count))), 10)
FROM table_name s
START WITH s.ROWID = t.ROWID
CONNECT BY PRIOR parent_id = id ) AS count_product
FROM table_name t
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
| ID | PARENT_ID | COUNT | COUNT_PRODUCT |
|---|---|---|---|
| 1 | null | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 4 | 2 | 4 | 8 |
| 3 | 1 | 3 | 3 |
| 5 | 3 | 5 | 15 |
| 6 | 5 | 6 | 90 |