MySQL query: How to calculate the average of values in a row?

Question

The AVG() function calculates column-means but how can I retrieve the mean of several values in the same row like so:

SELECT MEAN(A.a, A.b, ... , A.n) FROM A;

EDIT: sure, as cherouvim suggests I can do:

SELECT MEAN(A.a + A.b + ... + A.n) / n FROM A;

but I'm trying to find out if there's a leaner way.

Answer 1

select (A.a + A.b) / 2 from A;

Answer 2

I stumbled upon a similar situation. This came useful: http://tech-blog.borychowski.com/index.php/2009/02/mysql/average-value-in-a-row/

From the page:

When we do:

SELECT *, (V.rank_0 + V.rank_1 + V.rank_2) / 3
AS row_avg FROM voting V

we only receive correct averages for the rows where all values are not NULL. But when I have e.g. 3, NULL, 4 I’d like to get 3.5 as a return. That’s the moment when function COALESCE() comes handy.

What does COALESCE () do? From MySQL manual we have:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL

And these information will help us to build another SELECT statement:

SELECT *,

#first part
(COALESCE(V.rank_0, 0)
+ COALESCE(V.rank_1, 0)
+ COALESCE(V.rank_2, 0))
/

#second part
(3 -
(COALESCE(V.rank_0 - V.rank_0, 1)
+ COALESCE(V.rank_1 - V.rank_1, 1)
+ COALESCE(V.rank_2 - V.rank_2, 1))
) AS row_avg FROM voting V