Average of three values 1

[evergrean100]

I have three fields with around 100 records in my Access 2000 database where each field has a value and I need to get average.

tableid fieldOne   fieldTwo   fieldThree
1          2          3          1
2          2          1          2
3          1          2          1
--------------------------------------
Total      5          6          4

The average of the data above would be 5: (5 + 6 + 4)/3 = 5


Please advise the query because my attempt is not working:

select avg(tot(fieldOne) + tot(fieldTwo) + tot(fieldThree)) as myAvg
from tableOne;

 

[lespaul]

select avg(SUM(fieldOne) + SUM(fieldTwo) + SUM(fieldThree)) as myAvg
from tableOne;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[AlexCuse]

Look up 'avg' function in the help file, and you will see this:

Operands in expr can include the name of a table field, a constant, or a function (which can be either intrinsic or user-defined but not one of the other SQL aggregate functions).

Is 'tot' a user defined function designed to replace SUM?

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

good to know, I was just guessing!

les

 

[evergrean100]

I tried this:
select avg(SUM(fieldOne) + SUM(fieldTwo) + SUM(fieldThree)) as myAvg
from tableOne;

and it gave me error saying "Cannot have aggregate function in expression"

Please advise.

 

[AlexCuse]

Leslie - Hehe, only reason I found out is because I tested the same thing you posted. Didn't mean that one for you, I had not read your post.

EverGrean - Avg function is meant to average an entire column. So

avg(FieldOne + FieldTwo + Field3)

might return what you are looking for. I would suggest adding a column to your query and setting it to

((fieldOne + FieldTwo + Field3)/3)

as this may be more likely to return the result you are looking for. You might need to use format function to get this to return a decimal as well, I am not sure on that though.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[evergrean100]

Thanks,

It does not give me average of all three tables. It keeps giving me high numbers. It works if I just try and get avg of one field but need average of all fields.
Please advise as I have tried all queries suggested.

 

[AlexCuse]

Is the average all you want your query to return?

If so, try this:

select ((sum(FieldOne) + sum(FieldTwo) + sum(FieldThree))/3) from TableOne

Average function will return average of values in the column.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[evergrean100]

Thanks