Re: Compute weighted average

Michel Cadot wrote:
> "Robert Klemme" <shortcutter_at_googlemail.com> a écrit dans le message de news: 4omnddFf3iiuU1_at_individual.net...
> | On 06.10.2006 11:17, Alex wrote:
> | > Follow up:
> | > I forgott to mention that the weights of which the values are weighted
> | > are in the same table:
> | >
> | > Name | Month | Value | Weight | WAVG (Factor)
> | > -----|-------|-------|--------|-------------------
> | > A | 1 | 10 | 12 | 10,00 (10 * (12/12)
> | > B | 2 | 20 | 23 | 16,57 ( 10*(12/35) + 20*(23/35) )
> | > C | 3 | 40 | 56 | 32,31 ( 10*(56/91) + 20*(23/91) +
> | > 40*(59/91) )
> | > D | 4 | 30 | 33 | ...
> | >
> | > Can anyone tell me wether there is a function to which I can hand over
> | > VALUE, WEIGHT and RANGE and which'll return the weighted average?
> |
> | I'm not too experience with analytic functions but I guess you can order
> | by month and name and then use something like AVG(weight * value) /
> | SUM(weight)
> |
> | HTH
> |
> | robert
>
> I think you meant SUM(weight * value) / SUM(weight):

Right.

> SQL> select month, value, weight,
> 2 sum(weight*value) over (order by month) /
> 3 sum(weight) over (order by month) wavg
> 4 from t
> 5 order by month
> 6 /
> MONTH VALUE WEIGHT WAVG
> ---------- ---------- ---------- --------
> 1 10 12 10.00
> 2 20 23 16.57
> 3 40 56 30.99
> 4 30 33 30.73
>
> 4 rows selected.

Can't you put them together into a single "over" clause?

select month, value, weight,

        sum(weight*value) / sum(weight) over (order by month) wavg from t
order by month

Or am I missing something here?

        robert Received on Fri Oct 06 2006 - 14:18:31 CDT