Averages

G

Guest

I need to create a query that will give me averages on a list of numbers.

How can I calculate the averages, but ignore 0 or null values. Addtionally
if I am ignoreing the 0 value I don't want the record field to factored in
the average count.

e.g.

5
0
2
4

In this senario I would want the averages to be be caluated on the 3 records
with values in excess of 0., 5+2+4=11 11/3 =3.6

If I took the 0 value I would end up with an average of 11/4=2.75

Any help would be appreciated.
 
G

Guest

You could do two queries. One to exclude what you don't want in the calc,
then the second to perform the calc.

Steve Clark, Access MVP
FMS, Inc
 
J

Jeff Boyce

Robert

Are all your values in the same field, multiple rows, or are you trying to
average like you might a spreadsheet (multiple columns of numbers in the
same row)?

Regards

Jeff Boyce
<Access MVP>
 
M

Michel Walsh

Hi,



SELECT AVG(fieldName)
FROM tableName
WHERE fieldName <> 0


will return 3.6. The WHERE clause is applied before the aggregations
occurred. If you need a criteria to be applied AFTER the aggregation
occurred, you use a HAVING clause. Most aggregate also remove the NULL
(unknown) values from consideration.


Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top