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, 2, 3 FROM DUAL UNION ALL
SELECT 4, 3, 4 FROM DUAL UNION ALL
SELECT 5, 4, 5 FROM DUAL UNION ALL
SELECT 6, 5, 6 FROM DUAL;
6 rows affected
WITH result_table (id, parent_id, count, count_product) AS (
SELECT id, parent_id, count, count FROM table_name WHERE parent_id IS NULL
UNION ALL
SELECT t.id, t.parent_id, t.count, t.count * r.count_product
FROM result_table r
INNER JOIN table_name t
ON (r.id = t.parent_id)
)
SELECT *
FROM result_table;
| ID | PARENT_ID | COUNT | COUNT_PRODUCT |
|---|---|---|---|
| 1 | null | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 2 | 3 | 6 |
| 4 | 3 | 4 | 24 |
| 5 | 4 | 5 | 120 |
| 6 | 5 | 6 | 720 |