|Subject||Re: Weighted Averages|
|Author||Francois Du Toit|
> (SELECT SUM(t1.val1 * t1.weight1)/SUM(t1.weight1)
> FROM tbl t1
> WHERE t1.val1 <> 0) as avg1,
> (SELECT SUM(t2.val2 * t2.weight1)/ SUM(t2.weight1)
> FROM tbl t2
> WHERE t2.val2 <> 0) as avg2
> from tbl t3
> WHERE (row limiting criteria...)
This does the trick! As can be expected, performance drops (by about
40-50%) from a normal AVG() as each row has to be fetched 3 times.
But, this will be perfect when a stored proc is an overkill.
I am still a bit unsure about the aggregate UDFs. Do I understand
correctly that it is not possible at this stage?
Thanks for the help