Return average of non zero values in query

[Randy Harris]

How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

[Squirrel]

Could you base your average on the values returned by a query which selects
only values > 0?
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:06*****************@newssvr33.news.prodigy.co m...

How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

[Randy Harris]

"Squirrel" <wi*****@covad.net> wrote in message
news:7e**************************@msgid.meganewsse rvers.com...

Could you base your average on the values returned by a query which selects only values > 0?
The report is based on a query that computes Count and Avg on about 40
different fields. The query needs to return all of the records because it
might have a zero value in one field and an actual value in another.

The data is survey returns. Each field will have a value of 1 to 7, but can
be 0 (did not answer). I need to ignore the zero entries in getting the
counts and averages.

Randy
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:06*****************@newssvr33.news.prodigy.co m...

How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

[Roberto Spier]

"Randy Harris" <ra***@SpamFree.com> escreveu na mensagem
news:K2*****************@newssvr17.news.prodigy.co m...

"Squirrel" <wi*****@covad.net> wrote in message
news:7e**************************@msgid.meganewsse rvers.com...

Could you base your average on the values returned by a query which selects

only values > 0?

The report is based on a query that computes Count and Avg on about 40
different fields. The query needs to return all of the records because it
might have a zero value in one field and an actual value in another.

The data is survey returns. Each field will have a value of 1 to 7, but

can be 0 (did not answer). I need to ignore the zero entries in getting the
counts and averages.

Randy

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:06*****************@newssvr33.news.prodigy.co m...

How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

Hi,

just a thought:

SELECT Sum(field) / -Sum(field <> 0 ) as myAVG
FROM table;

bob

[Mark]

Hi,
The only way I could figure out how to do this was to run 1 query:
SELECT ur_table.*
FROM ur_table
WHERE (((ur_table.ur_field)<>0));

and then run a second query using the first query as the source:

SELECT Avg(Query1.ur_field) AS AvgOfur_field
FROM Query1;

There maybe a better way of doing this which others may point out but thats the best I could come up with :o)

Mark
"Randy Harris" <ra***@SpamFree.com> wrote in message news:06*****************@newssvr33.news.prodigy.co m...
How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

[Trevor Best]

Randy Harris wrote:

How can I report an average of non zero values?

If the values are:

5, 0, 6, 0, 4

I would like the result 5 (15 / 3), not 3 (15 / 5)

Thanks for any help...

(f1+f2+f3+f4+f5) / ((f1>0+f2>0+f3>0+f4>0+f5>0)*-1)