Helen

In the example data
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 weights of the zero
values should not form part of the corresponding weighing.

SELECT SUM(val1*weight1)/SUM(weight1) avg1, SUM(val2*weight1)/
SUM(weight1) avg2 FROM tbl.
will give 5.17 for val1 and 2.33 for val2. The problem is that the
weight of row 3 should not be added for the total weight for val1. So
I could write
SELECT SUM(val1*weight1)/SUM(weight1) FROM tbl WHERE val1<>0.
This will give to correct answer of 6.2.

The problem I have is to return both values correctly with 1 query.
SELECT SUM(val1*weight)/SUM(weight1) avg1, SUM(val2*weight1)/
SUM(weight1) avg2 FROM tbl WHERE val1<>0 and val2<>0
will give 4.33 for val1 and 4 for val2. This is incorrect as both
row 3 and 4 are ignored.

Subsequent to your reply, I have read an article on IBPhoenix
(http://ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_act_db.).
According to the article, UDF can not be used in aggregate functions
as the UDF does not maintain state between calls. I guess I was
hoping that there is a work-around :)

I currently use stored procedures (and have been doing so for just
over a year), but the maintenance is high. I need to use it in many
different scenarios (each with a different set of conditions) and I
end up with several stored procs looking almost identical. Ideally, I
would like to be able to use a query in cases where I do not return
too many rows or where I would not use the specific set of conditions
too often.

I hope my explanation is clearer this time around!

Regards,
Francois