At 10:17 AM 2/11/2004 +0000, you wrote:

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

That is what you would expect, since your WHERE clause tells it to ignore
rows that have 0 in either val1 or val2.

You could try this:

select
SUM(t1.val1 * t1.weight1)/SUM(t1.weight1) as avg1,
(SELECT SUM(t2.val2 * t2.weight1)/ SUM(t2.weight1)
FROM tbl t2
WHERE t2.val2 <> 0) as avg2
FROM tbl t1
WHERE t1.val1 <> 0

./heLen