Problem with sum and group by while joining 1

[shahdhruv]

Hi folks. Need help with something conceptual.

My table- zrpt_goals has data of goals based on rpt_group, month, year . I need goals for this month, previous 3 months, and full year.
So I use
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
group by zrpt_goal, tgoal

this is working fine. I need to join this to pm table. There are many pmnum mapping to a zrpt_group, and when I join, sum is getting messed up.

I need something like

select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals g, pm
where zfyear = 2006
and pm.zrpt_group = g.zrpt_group
group by zrpt_group, tgoal

I have tried using distinct, qualifying the columns. Tried thinking of using intersect. There is one thing which works but doesnt do all i want. If I use corelated query,

select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
and zrpt_group in
(select zrpt_group from pm)

but I need pm.eqnum which will join with other tables and check conditions but that is the next chapter.
Any ideas. Stuck real bad.

Thanks,
Dhruv.

 

[SantaMufasa]

Dhruv,

Try this code and see if it does what you want:

select zrpt_group, tgoal, tgoal5_7, tgoal
from (select zrpt_group
            ,tgoal
            ,sum(case when zfmonth between 5 and 7
                      then tgoal end) tgoal5_7
            ,sum(tgoal) tgoal
        from zrpt_goals
       where zfyear = 2006
       group by zrpt_group, tgoal) g
     ,pm
where pm.zrpt_group = g.zrpt_group;

Let us know the results of this query adjustment.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[shahdhruv]

Super.
I had to use two inline views, and join those views - its a killer.
Thanks,
Dhruv.