Skip to Main Content

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!

newbie- Calculating weighted average

Steve42Dec 17 2008 — edited Dec 18 2008
create table testweight(amonth numeric(2,0)
,ahours numeric(6,0)
,fiscalyear number(4,0));
insert into testweight(amonth,ahours,fiscalyear) values(1,30,2009);
insert into testweight(amonth,ahours,fiscalyear) values(2,60,2009);
insert into testweight(amonth,ahours,fiscalyear) values(1,10,2009);
insert into testweight(amonth,ahours,fiscalyear) values(2,5,2009);

Select fiscalyear,amonth,SUM(sumhours) sum2hours
FROM (
Select fiscalyear,amonth,SUM(ahours) OVER (order by amonth) sumhours
from testweight
)
group by fiscalyear,amonth;


I want the output to look like
MONTH Total HOURS Avg hours
1 40 .380 which is 40/105
2 65 .619 which is 65/105


We are using Oracle 9I. I tried using the LAG and the DECODE, but no luck.
SO I need to figure out some way to get the total ( 105) on each row group by amonth.


Any help would be appreciated.

TIA.
Steve
This post has been answered by JustinCave on Dec 17 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 15 2009
Added on Dec 17 2008
2 comments
481 views