Mysql – Obtaining the average of three columns per row in SQL

aggregateMySQL

The AVG() function in SQL works particular column data. But here, we want to calculate the average of three such columns for each row. In math, we would do 

AVG=(col1 + col2 + col3)/3

Similarly: is there any query to calculate AVG(col1, col2, col3...)?

Best Answer

If the columns aren't nullable then simply using 

(col1 + col2 + col3)/3

will work fine (though on some RDBMSs you might need to have a non integer numerator or divisor to avoid integer division).

For nullable columns you might want to use something like

SELECT CASE
         WHEN COALESCE(col1, col2, col3) IS NOT NULL THEN 
         ( COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) ) / 
            (CASE WHEN col1 IS NULL THEN 0 ELSE 1 END + 
             CASE WHEN col2 IS NULL THEN 0 ELSE 1 END + 
             CASE WHEN col3 IS NULL THEN 0 ELSE 1 END)
       END

On SQL Server you could also use

SELECT *,
       (SELECT AVG(Col)
        FROM   (VALUES(Col1),
                      (Col2),
                      (Col3)) V(Col)) AS col_average
FROM   YourTable
Related Question