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

If no one else finds a better solution, you could always just add them together, then divide by the number of columns you added.

Answer 3

Not pretty, but it works.

Not specifically MySql, but the idea should be easy enough to translate over.

CREATE TABLE A (id int identity(1,1), C1 int, C2 int, C3 int)
GO

INSERT INTO A VALUES (1,1,1)
INSERT INTO A VALUES (2,2,2)
INSERT INTO A VALUES (3,3,3)
INSERT INTO A VALUES (1,2,3)
INSERT INTO A VALUES (4,5,6)
GO

CREATE VIEW A_Values
AS
SELECT ID, AVG(Val) AS Average 
FROM
(
    SELECT ID, C1 AS Val FROM A
    UNION ALL
    SELECT ID, C2 AS Val FROM A
    UNION ALL
    SELECT ID, C3 AS Val FROM A
) Q
GROUP BY ID
GO


SELECT * FROM A_Values
GO

Answer 4

I'm not familiar with the MySQL syntax, but what about dumping the row data into a temporary table as multiple rows with a single column, and then using the AVG() function to obtain your result?