By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Return average of non zero values in query

P: n/a
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...
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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...

Nov 13 '05 #2

P: n/a

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


Nov 13 '05 #3

P: n/a

"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

Nov 13 '05 #4

P: n/a
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...
Nov 13 '05 #5

P: n/a
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)

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.