Oracle Hierarchical query with multiple Parent Levels : how to get required details

Question

I have a table which stores Hierarchy of Customers i.e., GrandParent, Parent and Child as follows

CustomerNum     Amount  GrantParent   Parent           
-----------     ------  -----------   ------ 
8046026507      100     NULL          1872539355
8099032159      100     1872539355    8046026507
1872539355      100     NULL          NULL

I need a SQL query to get the Sum of AMOUNT when I pass a Customer based on the Hierarchy. For Example When I pass customer '1872539355'(GrandParent) I should get '300' (Sum of all parent, child) of that customer. When I pass '8046026507'(Parent) I should get '200'. When I pass 8099032159(Child) I should get '100'. One Grand Parent Can have multiple Parents and One Parent Can have multiple childs.

Many thanks in Advance.

Answer 1

Assuming that you have a tree data structure (without loops or branches merging) then use a hierarchical query:

SELECT SUM(amount)
FROM   table_name
START WITH CustomerNum = 1872539355
CONNECT BY PRIOR CustomerNum = Parent

Note: you do not need the grandparent information - just the parent.

Which, for the sample data:

CREATE TABLE table_name (CustomerNum, Amount, GrantParent, Parent) AS
SELECT 8046026507, 100, NULL,       1872539355 FROM DUAL UNION ALL
SELECT 8099032159, 100, 1872539355, 8046026507 FROM DUAL UNION ALL
SELECT 1872539355, 100, NULL,       NULL       FROM DUAL;

Outputs:

SUM(AMOUNT)
300

If you START WITH CustomerNum = 8046026507 then the output is:

SUM(AMOUNT)
200

and if you START WITH CustomerNum = 8099032159 then the output is:

SUM(AMOUNT)
100

fiddle