Averages

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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>
 
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
 
Back
Top