Average of Average in one row

Question

Hi there,

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

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

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

SELECT avg( col1, col2, col3) FROM tbl

doesn't work at MySQL 5.1

Answer 1

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

Answer 2

Basic maths:

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

Answer 3

Did you try:

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

You must check that there are no nulls in this columns.

Answer 4

Just trying to improve on Anthony and zendar

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

Assumptions:

• all values have same signifance (weight)
• there are nulls
• you always want correct result

Potential problems:

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

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

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

Answer 5

Just for fun, here's a bit different solution that leaves NULL handling to 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 put in one virtual column and then database calculates average.

You don't have to worry about NULL values - database and avg() function will do it for you.

Note: This could be slower than other solutions since it can cause 3 full table scans to create virtual table. Check execution plan.