I have data in coloumns like so
col_1 col_2 col_3
1 3 5
4 3 10
2 5 2
5 5 5
etc...

I need a sql query that will average each row of these three columns,
so I will get an out put like so
3
5.667
3
5

I have tried to figure it out, but I've failed. Does anybody know of a
way to do this? TIA

method 1
Using sql, it is easy to get an average by column, just use the avg
function - ex: avg([col1]).
If you flip row and columns with a crosstab query and then write these
results out to a table
using a make table query, you could then use the avg on the resulting
columns.

method 2
In a query, use a formula to calculate two new columns: total & average
total=[col1]+[col2] + ...etc.
average=[total]/#columns

Is there some reason for your observations to be in columns instead of rows?
You have an upper limit of 255 columns, but no limit for rows.

Art Yates



"mahnovetsky" <mahnovetsky (AT) yahoo (DOT) com.au> wrote

Thanks Mr Art Yates, thats exactly what I wanted. I used method 2 and
it worked beautifully.

"Art Yates" <aeyates (AT) workerscomp (DOT) com> wrote

There is secret to doing SQL without knowing anything about it.
In this case write a summation query and use the AVG setting.
Then look at the SQL view and you'll see the SQL code.

I did one with 2 cols and this is what it looks like:

SELECT Avg(tblRVU.MPRVU) AS AvgOfMPRVU, Avg(tblRVU.FeeCharge) AS
AvgOfFeeCharge
FROM tblRVU;

Good Luck
Ira Solomon

On 4 Aug 2003 08:11:40 -0700, mahnovetsky (AT) yahoo (DOT) com.au (mahnovetsky)
wrote: