Francois,

If you had an iff(boolean_expression, true_result, false_result) you could
try:

SELECT SUM(val1*weight1)/SUM(weight1 * iff(val1 > 0, 1, 0)) avg1,
SUM(val2*weight2)/SUM(weight2 * iff(val2 > 0, 1, 0)) avg2
FROM tbl

This would eliminate the value weightx where valx = 0.

I don't know if such a UDF already exists or not.

Bill Meaney

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