Subject | Re: Weighted Averages |
---|---|

Author | Francois Du Toit |

Post date | 2004-11-02T14:13:18Z |

> selectHelen

> (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

Francois