Skip to Main Content

Oracle Database Discussions

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!

Calculating an Avg in SQL

738479Jul 30 2010 — edited Jul 30 2010
I'm calculating an average with the following query:

Select Avg(Final_Value)
into v_3Month_Avg
From
-- Prior Month
(Select Pa_Entered_Value as Final_Value
from wr_measure_value
where wr_measure_oid= p_MeasureOID
and project_associate_oid = p_ProjectAssociateOID
and period_oid = (p_PERIOD_OID - 1)
and wr_item_oid = p_ItemOID
UNION
-- 2 Months Prior
Select Pa_Entered_Value as Final_Value
from wr_measure_value
where wr_measure_oid= p_MeasureOID
and project_associate_oid = p_ProjectAssociateOID
and period_oid = (p_PERIOD_OID - 2)
and wr_item_oid = p_ItemOID
UNION
-- Current Month (value passed in)
Select p_PA_ENTERED_VALUE as Final_Value From Dual
);


In the union, the first select produces a 0, the second produces a 0 and the third produces 34452. The average then is being calculated as 17226. I don't think this is correct though because

(34452 + 0 + 0) / 3 = 11484. Since two values are the same, the query is counting them as 1 item and taking (34452 + 0) /2 What would you do about this to get the correct average?
This post has been answered by Tubby on Jul 30 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 27 2010
Added on Jul 30 2010
3 comments
983 views