At 10:26 PM 2/11/2004 +1100, I wrote:

>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

Oops, no, you'll need two subqueries because the conditioning on the main
statement will still cause the val2=0 row to be ignored.

So, amendment:

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

./hb