OraFAQ Forum: SQL & PL/SQL » SUM of parent and child records

Home » SQL & PL/SQL » SQL & PL/SQL » SUM of parent and child records (11.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SUM of parent and child records [message #684394]

Sun, 23 May 2021 23:55

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi All,

I have data like below.

WITH xxc_test AS
(
(select 1010 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
union all
select 2020 customer_trx_id ,'Credit memo' type,-50 SELLING_PRICE,1010 previous_customer_trx_id from dual
union all
select 3030 customer_trx_id ,'Invoice' type,50  SELLING_PRICE,null previous_customer_trx_id from dual
union all
select 4040 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,3030 previous_customer_trx_id from dual
union all
select 5050 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
union all
select 6060 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,5050 previous_customer_trx_id from dual
union all
select 7070 customer_trx_id ,'Credit memo' type,-20 SELLING_PRICE,5050 previous_customer_trx_id from dual
)

Stroing parent record reference value in previous_customer_trx_id
Example:
1010 is a parent record
2020 is a child record

join and get the parent , child records as provided in above.
I want to SUM parent + child records and see ZERO if ZERO then don't display.
Finally I want to populate only 3030,4040 records.

Thank you

Report message to a moderator

Re: SUM of parent and child records [message #684395 is a reply to message #684394]

Mon, 24 May 2021 00:36

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't understand "I want to populate only 3030,4040 records" as 4040 is not a parent.
So result for all parents:

SQL> with
  2    data as (
  3      select 1010 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
  4      union all
  5      select 2020 customer_trx_id ,'Credit memo' type,-50 SELLING_PRICE,1010 previous_customer_trx_id from dual
  6      union all
  7      select 3030 customer_trx_id ,'Invoice' type,50  SELLING_PRICE,null previous_customer_trx_id from dual
  8      union all
  9      select 4040 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,3030 previous_customer_trx_id from dual
 10      union all
 11      select 5050 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
 12      union all
 13      select 6060 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,5050 previous_customer_trx_id from dual
 14      union all
 15      select 7070 customer_trx_id ,'Credit memo' type,-20 SELLING_PRICE,5050 previous_customer_trx_id from dual
 16    )
 17  select customer_trx_id parent_customer,
 18         SELLING_PRICE +
 19         (select sum(SELLING_PRICE) from data b
 20          connect by prior b.customer_trx_id = b.previous_customer_trx_id
 21          start with b.previous_customer_trx_id = a.customer_trx_id) sum_price
 22  from data a
 23  where previous_customer_trx_id is null
 24  order by customer_trx_id
 25  /
PARENT_CUSTOMER  SUM_PRICE
--------------- ----------
           1010          0
           3030         20
           5050          0

3 rows selected.

And removing 0:

SQL> with
  2    data as (
  3      select 1010 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
  4      union all
  5      select 2020 customer_trx_id ,'Credit memo' type,-50 SELLING_PRICE,1010 previous_customer_trx_id from dual
  6      union all
  7      select 3030 customer_trx_id ,'Invoice' type,50  SELLING_PRICE,null previous_customer_trx_id from dual
  8      union all
  9      select 4040 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,3030 previous_customer_trx_id from dual
 10      union all
 11      select 5050 customer_trx_id ,'Invoice' type,50 SELLING_PRICE,null previous_customer_trx_id from dual
 12      union all
 13      select 6060 customer_trx_id ,'Credit memo' type,-30 SELLING_PRICE,5050 previous_customer_trx_id from dual
 14      union all
 15      select 7070 customer_trx_id ,'Credit memo' type,-20 SELLING_PRICE,5050 previous_customer_trx_id from dual
 16    ),
 17    sum_prices as (
 18     select customer_trx_id parent_customer,
 19            SELLING_PRICE +
 20            (select sum(SELLING_PRICE) from data b
 21             connect by prior b.customer_trx_id = b.previous_customer_trx_id
 22             start with b.previous_customer_trx_id = a.customer_trx_id) sum_price
 23      from data a
 24      where previous_customer_trx_id is null
 25    )
 26  select * from sum_prices where sum_price != 0
 27  order by parent_customer
 28  /
PARENT_CUSTOMER  SUM_PRICE
--------------- ----------
           3030         20

1 row selected.

Report message to a moderator

Previous Topic:	Cents corrupted on large number
Next Topic:	Present value calculations in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 01 06:09:35 CDT 2023