Subject | RE: [firebird-support] Weighted Averages |
---|---|

Author | Bill Meaney |

Post date | 2004-11-02T14:36:47Z |

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

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

>

>

>