CODEWITHSUNDEEP
sqloracle-databaseoracle11g
MKN
MKN

Reputation: 497

Sum all the child level data

I have a requirement where I need to sum up all the child of a node.

enter image description here

And the Output should be

D023343 120
D022959 120
D023375 50
D035738 10
D024481 0

I tried using diff queries but it is not getting the required . Kindly help

SELECT * FROM (SELECT a.child achild,b.child bchild,a.parent aparent,b.parent bparent,a.sales,b.sales
 FROM TEST a,TEST b
WHERE a.CHILD='D023375')
where aparent!=bchild
and achild=bparent

Kindly help.

Thanks

Upvotes: 0

Views: 1450

Answers (2)

trincot
trincot

Reputation: 351369

You can use a recursive common table expression as defined in the SQL-99 standard. Oracle calls this recursive subquery factoring. You'll also need to do something to get the last record with 0 sales in the result, since there is no record that explicitly defines that sales amount.

Here is how such a query would look:

with base(parent, child, sales) as (
    select parent, child, sales
    from   tbl
    union all -- add the 0 sales records
    select child, null, 0
    from   tbl
),
rec(parent,child,sales) as ( -- recursive query
    select parent, child, sales
    from   base
    union all
    select rec.parent, base.child, base.sales
    from   base
    inner join rec on base.parent = rec.child
)
select   parent, sum(sales)
from     rec
group by parent
order by 1

NB: this syntax is supported as of Oracle 11g Release 2.

Upvotes: 1

MT0
MT0

Reputation: 168741

Use a hierarchical query to traverse the hierarchy and CONNECT_BY_ROOT() to reference the top of the hierarchy - you can then use that to group the query to get the totals of the descendants.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE your_table( parent, child, sales ) AS
SELECT 'D023343', 'D022959',  0 FROM DUAL UNION ALL
SELECT 'D022959', 'D023375', 60 FROM DUAL UNION ALL
SELECT 'D023375', 'D035738', 50 FROM DUAL UNION ALL
SELECT 'D035738', 'D024481', 10 FROM DUAL;

Query 1:

SELECT root,
       SUM( sales ) AS total_sales
FROM   (
  SELECT CONNECT_BY_ROOT( parent ) AS root,
         sales
  FROM   your_table
  CONNECT BY PRIOR child = parent
)
GROUP BY root

Results:

|    ROOT | TOTAL_SALES |
|---------|-------------|
| D023375 |          60 |
| D035738 |          10 |
| D023343 |         120 |
| D022959 |         120 |

If you want the leaf children that have no sales then you will need to do something like appending 

UNION ALL
SELECT child, 0 FROM your_table

to the inner query.

Upvotes: 2

Related Questions