How to Correctly Calculate the Average of Multiple Columns with NULL Values in SQL Server?

Average of Multiple Columns

When calculating the average of multiple columns in a table, it's crucial to handle nullability correctly. Consider the following situation:

Problem:

You have a table called "Request" with the following data:

Req_ID    R1   R2   R3   R4   R5
R12673    2    5    3    7    10
R34721    3    5    2    1    8
R27835    1    3    8    5    6

You want to display the average of the columns R1, R2, R3, R4, and R5. However, when you use the following query:

Select Req_ID, Avg(R1+R2+R3+R4+R5) as Average
from Request
Group by Req_ID

You get the sum of the values instead of the average.

Solution:

The issue lies in the handling of NULL values. If your columns allow NULLs, the sum of NULLs is also NULL. To calculate the average correctly, you need to ignore the NULLs.

Post-2008 Syntax:

If you're using SQL Server 2008 or later, you can use the following syntax:

SELECT *,
       (SELECT AVG(c)
        FROM   (VALUES(R1),
                      (R2),
                      (R3),
                      (R4),
                      (R5)) T (c)) AS [Average]
FROM   Request

This syntax uses the VALUES clause to construct a table with one row for each column. The AVG aggregate is then applied to this table, ignoring any rows that contain NULL values.

Pre-2008 Syntax:

If you're using SQL Server 2005 or earlier, you can use the following syntax:

SELECT *,
       (SELECT AVG(c)
        FROM   (SELECT R1
                UNION ALL
                SELECT R2
                UNION ALL
                SELECT R3
                UNION ALL
                SELECT R4
                UNION ALL
                SELECT R5) T (c)) AS [Average]
FROM   Request

This syntax uses UNION ALL to create a table with one row for each column. The AVG aggregate is then applied to this table, ignoring any rows that contain NULL values.

The above is the detailed content of How to Correctly Calculate the Average of Multiple Columns with NULL Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!