Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to group records of different values

user2081225Jul 21 2010 — edited Jul 23 2010
Hi,

I have few records in table in this form:

CDE_FUND_CODE| |CDE_CLM_TYPE| |NUM_PCT| |NUM_SVC_DAYS| |
===========| |==========| |======| |==========| |
3R2A | | I | | 0.7215 | | 1 | |
3R2A | | I | | 0.2785 | | 1 | |
3R2A | | I | | 0.7215 | | 2 | |
3R2A | | I | | 0.2785 | | 2 | |
3R2A | | I | | 1.0000 | | 1 | |
3R2A | | I | | 0.2598 | | 1 | |
3R2A | | I | | 0.2401 | | 1 | |
3R2A | | I | | 0.5001 | | 1 | |

Now the notice the values of NUM_PCT
1) Sum of num_pct of first 2 records is 1.
2) Sum of num_pct of records 3rd and 4th is also 1.
3) num_pct of 5th record is 1.
4) Sum of num_pct of records 6th, 7th and 8th is 1.

So I want to write a query which gives me total 4 rows and output should look like this:-

CDE_FUND_CODE| |CDE_CLM_TYPE| |NUM_PCT| |NUM_SVC_DAYS| |
===========| |==========| |======| |==========| |
3R2A | | I | | 1.0000 | | 1 | |
3R2A | | I | | 1.0000 | | 2 | |
3R2A | | I | | 1.0000 | | 1 | |
3R2A | | I | | 1.0000 | | 1 | |

Please let me know if you have any idea about how to write query to get this output.

Thanks,
Rohit

Comments

Frank Kulash
Answer
Hi,

Welcome to the forum!

Thanks for posting the CREATE TABLE and INSERT statements! That's very helpful.
It's also helpful if you format your code and output. When posting formatted text on this site, type these 6 characters:

\
(all small letters, inside curly brackets) to keep the site from compressing the spaces.

You can do two CONNECT BY queries: one to get the desired amount, and the other like you're already doing, to get the indented child column.  The first will differ in that it has no START WITH clause, which means every node in your table will be the root of a tree, and that there's an additional CONNECT BY condition to quit looking for descendants as soon as an amount is found.  This corresponds to your apparant requirement that the desired_amount is the amount, if it is not NULL, and only otherwise is the sum of the desired_amounts of one's descendants.
WITH desired_amount_tree AS
(
SELECT CONNECT_BY_ROOT child AS root
, child
, child_amount
FROM ent_rel
CONNECT BY PRIOR child = parent
AND PRIOR child_amount IS NULL
)
, got_desired_amount AS
(
SELECT root
, SUM (child_amount) AS desired_amount
FROM desired_amount_tree
GROUP BY root
)
select RPAD ( ' '
, (LEVEL - 1) * 2
, '-'
) || e.child AS child
, level
, e.child_amount
, desired_amount
from ent_rel e
JOIN got_desired_amount d ON e.child = d.root
start with parent is null
connect by prior child = parent
; 
It's usually more efficient to do join after doing the CONNECT BY.  For better performance, try doing your original query by itself, and then joining its result set to got_desired_amount.
Of course, it would be a lot more efficient if we could eliminate the extra CONNECT BY query.  That might be possible using MODEL or (starting in Oracle 11.2) a recursive sub-query.
Marked as Answer by 529206 · Sep 27 2020
529206
Hi Frank,

Thanks a lot for the quick solution. It is working great and thanks for the tips too for better posting on this forum. Appreciate your help.

thanks and regards,
Knl.
Aketi Jyuuzou
I like scalar subQuery :D
create table ent_rel (parent integer,
child integer, child_amount integer);

insert into ent_rel
select null, 100, null from dual
union all select 100, 101, null from dual
union all select 101, 102, 20 from dual
union all select 102, 103, null from dual
union all select 103, 104, null from dual
union all select 104, 105, null from dual
union all select 105, 106, 40 from dual
union all select 106, 107, null from dual
union all select 107, 108, 20 from dual
union all select 107, 109, 10 from dual
union all select 101, 203, null from dual
union all select 203, 205, 50 from dual
union all select 205, 207, null from dual
union all select 207, 209, null from dual
union all select 209, 210, 10 from dual;

col parent for a40

select Lpad('-',(Level-1)*2,'-') || child as parent,
Level,child_amount,
(select sum(b.child_amount)
   from ent_rel b
start with b.RowID = a.RowID
connect by prior child = parent
       and prior child_amount is null) as sumV
  from ent_rel a
start with parent is null
connect by prior child = parent;

PARENT               LEVEL  CHILD_AMOUNT  SUMV
-------------------  -----  ------------  ----
100                      1          null    70
--101                    2          null    70
----102                  3            20    20
------103                4          null    40
--------104              5          null    40
----------105            6          null    40
------------106          7            40    40
--------------107        8          null    30
----------------108      9            20    20
----------------109      9            10    10
----203                  3          null    50
------205                4            50    50
--------207              5          null    10
----------209            6          null    10
------------210          7            10    10
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 20 2010
Added on Jul 21 2010
6 comments
1,324 views