Home » SQL & PL/SQL » SQL & PL/SQL » SUM of parent and child records (11.2)
SUM of parent and child records [message #684394] Sun, 23 May 2021 23:55 Go to next message
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
Re: SUM of parent and child records [message #684395 is a reply to message #684394] Mon, 24 May 2021 00:36 Go to previous message
Michel Cadot
Messages: 68657
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.
Previous Topic: Cents corrupted on large number
Next Topic: Present value calculations in Oracle
Goto Forum:
  


Current Time: Fri May 24 11:30:55 CDT 2024