Re: Compute weighted average

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Fri, 6 Oct 2006 17:40:44 +0200
Message-ID: <452678fd$0$5695$426a74cc@news.free.fr>

"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):

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.

Regards
Michel Cadot Received on Fri Oct 06 2006 - 10:40:44 CDT