Subject | Weighted Averages |
---|---|

Author | Francois du Toit |

Post date | 2004-11-01T15:13:49Z |

I need to calculate the weighted average for two fields (val1 and val2)

using weight1. Zero values should be ignored.

val1 val2 weight1

---- ---- --------

5 8 10

4 2 20

0 2 10

9 0 20

should give: 6.2 for val1 and 3.5 for val2.

The following statements will not give the correct answers

SELECT SUM(val1*weight1)/SUM(weight1) avg1, SUM(val2*weight2)/SUM(weight2)

avg2 FROM tbl.

SELECT SUM(val1*weight)/SUM(weight1) avg1, SUM(val2*weight2)/SUM(weight2)

avg2 FROM tbl WHERE val1<>0 and val2<>0.

Val1 might be available, with val2 set to 0 or visa versa. I can not think

of any way to return both avg1 and avg2 with 1 query.

Is it possible to add UDF's for aggregate functions (maybe add a SUMIF)? Am

I missing the obvious?

Thanks

Francois du Toit

[Non-text portions of this message have been removed]

using weight1. Zero values should be ignored.

val1 val2 weight1

---- ---- --------

5 8 10

4 2 20

0 2 10

9 0 20

should give: 6.2 for val1 and 3.5 for val2.

The following statements will not give the correct answers

SELECT SUM(val1*weight1)/SUM(weight1) avg1, SUM(val2*weight2)/SUM(weight2)

avg2 FROM tbl.

SELECT SUM(val1*weight)/SUM(weight1) avg1, SUM(val2*weight2)/SUM(weight2)

avg2 FROM tbl WHERE val1<>0 and val2<>0.

Val1 might be available, with val2 set to 0 or visa versa. I can not think

of any way to return both avg1 and avg2 with 1 query.

Is it possible to add UDF's for aggregate functions (maybe add a SUMIF)? Am

I missing the obvious?

Thanks

Francois du Toit

[Non-text portions of this message have been removed]