Average of Average in one row

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, c开发者_StackOverflow社区ol2, col3) FROM tbl

doesn't work at MySQL 5.1

Did you try:

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

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

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

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

Basic maths:

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

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.

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 discard null values