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.

Group by multiple years

jfosteroracleAug 10 2010 — edited Oct 30 2010
I have an application where I need to sum a field by year PLUS the the previous 2 years. When I sum simply by year, it looks like this:

select s.survey_year. sum(ac.plover_calculated_adult) AS totalCalcAdults
from dms.adult_census ac,dms.sites s
where ac.site_id = s.site_id
group by s.survey_year

How do I group by year plus the previous 2 years? I'm at a loss for how to write that.

Thanks in advance!!

JOhn
This post has been answered by Frank Kulash on Aug 10 2010
Jump to Answer

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 Sep 8 2010
Added on Aug 10 2010
6 comments
1,423 views