Average from Multiple Columns

[rsmonkey]

Hi,

I've got all the columns i require in my query and and i've averaged these, however i need to create a final column which contains the Average of all the other Column Averages.. if you get what i mean!
Put it this way i have columns A, B, C. All the numbers containd within each column have been averaged so now each column contains a single value. ie.

A = 10
B = 20
C = 9

So now i have to create column D which will contain the average of those results above i.e.:

D = 13

I would preferable like to contain this within a single query rather than having to write a vb module and having to add extra tables ect.. but that might be the only way!

So if anyone could tell me the best way of achieving this it would be much appreciated. I've tried a couple things like concatenation:
Average: [Score1]&[Score2]&[Score3]

but this hasnt work.. no surprises really!

Cheers

 

[Darth Vodka]

Average: [Score1]&[Score2]&[Score3]

i'm tempted to say MyAverage[Score1]+[Score2]+[Score3])/3

but... are you going to be having nulls and zeros in the records?

do want the avergage of {null, 1, 3} to be 1.33 or 2 ?

oh and "Average" may well be a reserve word

 

[boblarson]

This SHOULD work for you:

MyColumnD:Avg(Nz([Score1],0)+Nz([Score2],0)+Nz([Score3],0))

 

[KenHigg]

Try something like the following:

MyNewField: (avg([Col1] + avg([col2]) + avg([col3]))/3

Edit: Bob's Version and my version are different and will give two different values -

 

[boblarson]

Try something like the following:

MyNewField: (avg([Col1] + avg([col2]) + avg([col3]))/3

Ken:

Sorry to be a stick-in-the-mud, but that doesn't make sense at all.

If you are creating a column, then avg([Col1]) will be the same number as just [Col1] (and the same for col2 and col3)as it is taking the average of a single value.

My code, of:

MyColumnD:Avg(Nz([Score1],0)+Nz([Score2],0)+Nz([Score3],0))

Will return an average score of each column added together and then divided by the number of columns. Now Darth's question about nulls is valid. Do they want to return a 0 (which my code does) if it is null and then it calculates into the avg as a zero amount. Or, do they want to only avg those columns with amounts. If that is the case then I think a custom function is needed.

 

[KenHigg]

Ken:

Sorry to be a stick-in-the-mud, but that doesn't make sense at all.

If you are creating a column, then avg([Col1]) will be the same number as just [Col1] (and the same for col2 and col3)as it is taking the average of a single value.

My code, of:

MyColumnD:Avg(Nz([Score1],0)+Nz([Score2],0)+Nz([Score3],0))

Will return an average score of each column added together and then divided by the number of columns. Now Darth's question about nulls is valid. Do they want to return a 0 (which my code does) if it is null and then it calculates into the avg as a zero amount. Or, do they want to only avg those columns with amounts. If that is the case then I think a custom function is needed.

No problem Bob. But I think my version gives him what he wants.

 

[boblarson]

Actually, I think we BOTH had it wrong. I believe he wants an average for each row and to get that he WILL need:

MyColumnD: ([col1]+[col2]+[col3])/3

 

[boblarson]

See Query3 for the results

 

[Darth Vodka]

Actually, I think we BOTH had it wrong. I believe he wants an average for each row and to get that he WILL need:

MyColumnD: ([col1]+[col2]+[col3])/3

i was just gonna say that

avg is an aggregate function and i think he's done the grouping already

so i'd say:-

([col1]+[col2]+[col3])/3

if you want a null to cause an average of null

(nz([col1],0)+nz([col2],0)+nz([col3],0))/(3-iif(isnull([col1]),1,0)-iif(isnull([col2]),1,0)-iif(isnull([col3]),1,0))

if you want nulls excluded

and

(nz([col1],0)+nz([col2],0)+nz([col3],0))/3

if you want nulls counted as zeroes

of course the real question is, what are you doing with similar data in three columns as opposed to one

 

[KenHigg]

'All the numbers containd within each column have been averaged' leads me to think he's avg'd the values going down each column. Hence:

ColA     ColB     ColC
 x          x          x
 x          x          x
 x          x          x 
 x          x          x
 x          x          x

Average:
10        20         9

Hence:
Avg A = 10
Avg B = 20
Avg C = 9

And the Avg of these are (10+20+9)/3

Right?


ken

 

[Rabbie]

Remember you will get different answers if you take the average of averages rather than calculating the average from all the values.

 

[KenHigg]

Which way do you think is correct and why?

 

[Rabbie]

It depends on what your are looking for as to which is correct. If one average is derived using a larger number of entries than another then the correct average should be weighted in its favour. The average of averages willo be a less accurate figure.

For example

Col1 Col2 Average
10 5 2
40 10 4

Weighted average would be 50/15 = 3.333
average of averages would be 3

 

[KenHigg]

Aside from what happens when you average anything, etc, what do you think the original user was asking for? An average of the average of the columns or the average of the average of the rows? Or something else?

 

[ajetrumpet]

Hence:
Avg A = 10
Avg B = 20
Avg C = 9

And the Avg of these are (10+20+9)/3

Right?


ken

YES, right.

 

[KenHigg]

YES, right.

whew -

Thanks -

 

[ajetrumpet]

Ken,

Here's something from the numbers world that might fascinate you (or maybe it won't, since you're a genius anyway...

This...

SELECT cint(avg([column1])) AS whatever, cint(avg([column2])) AS whatever2, 
cint(avg([column3])) AS whatever3, 
cint(([column1]+[column2]+[column3])/[number of columns]) AS FINAL RESULT
FROM mytable;

yields the same as this...

SELECT cint(sum([column1])+sum([column2])+sum([column3])) as whatever, 
count(*) AS whatever2, 
cint(([whatever]/([whatever2]*[number of columns]))) as FINAL RESULT
FROM mytable;

 

[KenHigg]

That gives me headache....

How about:

SELECT cint(avg([column1])) AS whatever,
           cint(avg([column2])) AS whatever2,
           cint(avg([column3])) AS whatever3,
           cint(([column1]+[column2]+[column3])/[number of columns]) AS FINAL RESULT
FROM table1;

SELECT cint(sum([column1])+sum([column2])+sum([column3])) as whatever, 
           count(*) AS whatever2,
           cint(([whatever]/([whatever2]*[number of columns]))) as FINAL RESULT
FROM mytable;

Anyway - Hum... Interesting.


ken

 

[ajetrumpet]

what a profound nature you have. Don't mess with accounting people...we'll spin your head around...

Oh, and rsmonkey, I forgot to mention that I was actually trying to answer your original question too. This thread is too long as it is...

 

[KenHigg]

what a profound nature you have. Don't mess with accounting people...we'll spin your head around...

Oh, and rsmonkey, I forgot to mention that I was actually trying to answer your original question too. This thread is too long as it is...

rs may never in look back in -