Getting Averages for 5 Columns

Hi:

I’m trying to get the average of numerical entries in 5 different columns. Please note that I’m NOT talking about Rows, but columns. For example: how to get the AVG for columns A, B, and C.

A B C
Mon 23 34 36
Tues 56 58 73
Wed 78 12 25
Thur 88 45 68
Fri 67 32 18
AVG

I’ve been experimenting with the function AVG() but thus far no success.

Any suggestions?

Thank you for your attention

You can do simple math in a SQL statement without using a funciton:

SELECT (A+B+C/3) AS averageVal FROM table

Ray:

Your formula would give a “horizontal” average, meaning a ROW average, but the need is for a “vertical” average, meaning the average of all the entries in each column (NOT across columns)

In my example, the task would be to get the (“vertical”) average of column A, the average of column B, and the average of column C (NOT the average of column A+B+C)

Any suggestions?

Thank you for your attention

For that you would need to use the AVG() function.

SELECT Avg(A) as AAverage, Avg(B) as BAverage FROM Table

Ray:

Maybe the context in which I’m using the AVG() function is not the appropriate one, but I’m getting an error message saying: Unknown column ‘cwAverage’ in field list.

This is the SELECT I’m using:

SELECT oxadmain.name , oxadmain.year , oxadmain.photoID , oxadmain.thumbs , stuperfdaily.ID , stuperfdaily.stuid , stuperfdaily.date , stuperfdaily.subject , stuperfdaily.teacher , stuperfdaily.cw , stuperfdaily.hw , stuperfdaily.con , stuperfdaily.par , stuperfdaily.att, AVG(cw) as cwAverage, AVG(hw) as hwAverage, AVG(con) as conAverage, AVG(par) as parAverage, AVG(att) as attAverage, ROUND((cwAverage+hwAverage+conAverage+parAverage+attAverage)/5, 2) AS avg FROM stuperfdaily INNER JOIN oxadmain ON stuperfdaily.stuid=oxadmain.stuid GROUP BY stuperfdaily.stuid
ORDER BY oxadmain.stuid ASC

Any suggestions?

Just in case, attached please find a copy of the page in question.

Thank you for your attention

You can't refer to the calculated values later in the select statement like you are in the ROUND() function. You would have to use the AVG() function again in there like:

SELECT oxadmain.name , oxadmain.year , oxadmain.photoID , oxadmain.thumbs , stuperfdaily.ID , stuperfdaily.stuid , stuperfdaily.date , stuperfdaily.subject , stuperfdaily.teacher , stuperfdaily.cw , stuperfdaily.hw , stuperfdaily.con , stuperfdaily.par , stuperfdaily.att, AVG(cw) as cwAverage, AVG(hw) as hwAverage, AVG(con) as conAverage, AVG(par) as parAverage, AVG(att) as attAverage, ROUND((AVG(cw)+AVG(hw)+AVG(con)+AVG(par)+AVG(att))/5, 2) AS avg FROM stuperfdaily INNER JOIN oxadmain ON stuperfdaily.stuid=oxadmain.stuid GROUP BY stuperfdaily.stuid
ORDER BY oxadmain.stuid ASC

Ray:

Thank you for your reply. The error message is no more.

Thank you for your attention.