Average value on average per line

Question

I'm not sure if this is possible, what I'm trying to achieve. I want to get the average number of averaged columns.

SELECT avg(col1), avg(col2), avg(col3) FROM tbl 

My result should be the average of all three avg columns, is this possible? Something like that

 SELECT avg( col1, col2, col3) FROM tbl 

does not work in MySQL 5.1

Answer 1

Have you tried:

 SELECT avg( col1 + col2 + col3)/3.0 FROM tbl 

You should check that there are no zeros in the columns.

Answer 2

 SELECT (AVG(col1) * COUNT(col1) + AVG(col2) * COUNT(col2) + AVG(col3) * COUNT(col3)) / (COUNT(col1) + COUNT(col2) + COUNT(col3)) FROM tbl 

Answer 3

Just trying to improve Anthony and zendar

 SELECT (SUM(col1)+SUM(col2)+SUM(col3))/(COUNT(col1)+COUNT(col2)+COUNT(col3)) FROM tbl 

Assumptions:

• all values ​​have the same significance (weight)
• have zeros
• you always want the right result

Potential problems:

• for integer inputs, AVG does not overflow where SUM does, so an explicit cast may be necessary

EDIT (thanks redcayuga): If any of the columns is NULL for all rows, the above query returns NULL, so COALESCE should be applied to SUM

 SELECT (COALESCE(SUM(col1),0)+ COALESCE(SUM(col2),0)+ COALESCE(SUM(col3),0))/(COUNT(col1)+COUNT(col2)+COUNT(col3)) FROM tbl 

Answer 4

Basic mathematical data:

 SELECT AVG(col1 + col2 + col3) / 3 FROM tbl 

Answer 5

Just for fun, here is a slightly different solution that leaves the NULL control to the avg() function:

 SELECT avg(colValue) from ( SELECT col1 as colValue from tbl UNION ALL SELECT col2 as colValue from tbl UNION ALL SELECT col3 as colValue from tbl ) 

Values ​​from co1l, col2 and col3 are placed in one virtual column, and then the database calculates the average value.

You do not need to worry about NULL values ​​- the database function and avg() will do this for you.

Note. . This can be slower than other solutions, as it can cause 3 full table scans to create a virtual table. Check the execution plan.

Answer 6

 SELECT (ISNULL(AVG(col1),0) + ISNULL(AVG(col2),0) + .....) / (CASE WHEN AVG(col1) IS NULL THEN 0 ELSE 1 END + CASE WHEN AVG(col2) IS NULL THEN 0 ELSE 1 END +......) FROM tbl 

this will reset to zero