Calculating an Avg in SQL
738479Jul 30 2010 — edited Jul 30 2010I'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?