newbie- Calculating weighted average
Steve42Dec 17 2008 — edited Dec 18 2008create 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