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

Author | Helen Borrie |

Post date | 2004-11-02T11:26:37Z |

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

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

>HelenThat is what you would expect, since your WHERE clause tells it to ignore

>

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

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