View Question

[ulicki]

Hi,

I need to create a view that calculates weighted average from another table.

The fields I am using from the other table are
GRANT_NUM Number
OPTS_GRNTD Number
OPTS_VESTED Number
FAIR_VALUE Number

I need to calculate the weighted average FAIR_VALUE for each GRANT_NUM or set of GRANT_NUM (when there are more than one). OPTS_GRNTD is acutally equal to the sum of OPTS_VESTED for each grant number (so I guess I could divided by SUM(OPTS_VESTED) instead of MAX(OPTS_GRNTD). I hope I communicated the requirement correctly.

My experience with SQL is limited and not quite sure if I have this correct. Here is an example with real numbers.

GRANT_NUM OPTS_GRNTED FAIR_VALUE OPTS_VESTED
123 1000 3.0 500
123 1000 4.0 300
123 1000 6.0 200

So my weighted average would be
((500 * 3.0) + (300 * 4.0) + (200 * 6.0)) / 1000
which would be 3.9.

Is my view correct? Will it give the results I am looking for? BTW, OPTS_GRNTD will never be zero so I do not have to worry about divide by zero.


CREATE or REPLACE VIEW CUS_WEIGHTED_FAIRVALUE AS
SELECT
cf.GRANT_NUM FK_Grant_Number,
((SUM(cf.OPTS_VESTED * cf.FAIR_VALUE))/MAX(cf.OPTS_GRNTD)) Weighted_Fair_Value
FROM
cus_fasb cf
GROUP BY
cf.GRANT_NUM

 

[did02]

Hi,

Your code seems to be ok. But you can simplify it by using avg fonction :

CREATE or REPLACE VIEW CUS_WEIGHTED_FAIRVALUE AS
SELECT cf.GRANT_NUM FK_Grant_Number,
avg((FAIR_VALUE*OPTS_VESTED)/OPTS_GRNTED) Weighted_Fair_Value
FROM cus_fasb cf
GROUP BY cf.GRANT_NUM

It should give the same result.
Rgds,
Did02

 

[ChrisHunt]

ulicki, I think your query is correct. Don't use the AVG() function as it won't produce a weighted average.

For those without a stats background, those three rows are saying "500 trades with a value of 3, 300 with a value of 4 and 200 with a value of 6". To compute the average trade value you need to sum up trades x value and divide by the total number of trades. The Oracle AVG() function will divide by the number of distinct prices, which isn't relevant.

It won't be any more (or less) efficient, but I'd be inclined to calculate it as

SELECT grant_num,
       SUM(fair_value * opts_vested)/SUM(opts_vested) av_value
FROM   cus_fasb
GROUP BY grant_num

as it makes it a bit more explicit what's going on. Indeed I'd not store

opts_grnted

in the table at all if it were me. -- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[sem]

And of course, I'd suggest to use decode to process NULLs. Regards, Dima

 

[ChrisHunt]

Good point. Bullet-proof version:

SELECT grant_num,
       SUM(NVL(fair_value,0) * NVL(opts_vested,0))
       /SUM(NVL(opts_vested,0)) av_value
FROM   cus_fasb
GROUP BY grant_num

though I imagine the columns in question are probably defined as NOT NULL anyway.
-- Chris Hunt
Extra Connections Ltd

http://www.extracon.com

 

[sem]

Not Bullet-proof because of division by zero. I meant "outer" decode Regards, Dima

 

[ulicki]

Thanks all,

This really helps me feel confident with the code. I was unsure about using two functions in one expression and having the group by.

Thanks again.