CODEWITHSUNDEEP
sqloracle-database
psobhan
psobhan

Reputation: 645

How to get the highest parent amount from oracle

I have two tables customer and hierarchy as follows:

    Customer Table                             Hierarchy Table

    Pty Amount SID                             Parent    Child          
    100  10    01                                1         2
    200  20    02                                2         3
    100  20    03                                3         100
                                                 1         200

I want to get the total parent transaction amount but the parent does not have any data in customer table. How to map both tables by just using hierarchy query and get the all the child's values to parent.

My current query is as follows but does not serve my purpose properly

  Select Sum(c.amount),c.pty from customer c
    right outer join hierarchy h on c.pty=h.child    
    where h.parent in (select (m.child) as parent from hierarchy m
     Connect By Prior child=parent
     start with m.child=1
     )  group by c.pty

Result is as follows:    
Amount    Pty
30         2
null       1

But When I pass 1 as parameter I should get
Amount       Pty
50            1

When 2 is passed to the query, the result should be
Amount Pty
30      2

Any help is highly appreciated

Upvotes: 0

Views: 424

Answers (1)

Multisync
Multisync

Reputation: 8797

select root, sum(nvl(amount, 0)) s from (
  select level, h.child, h.parent, c.amount, connect_by_root h.parent root
  from customer c right join hierarchy h on c.pty = h.child    
  --start with h.parent = 1
  connect by prior child = parent
) group by root;

if you want to find sum for a certain parent just uncomment START WITH

connect_by_root is an unary operator which shows the root value of a hierarchy

This query starts with each row in hierarchy table and finds all children

connect_by_root helps to identify the root parent

Upvotes: 1

Related Questions