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]