7

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...)?

0

1 Answer 1

9

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.