MS-SQL Average Columns with NULL

Question

So I've got 3 different columns (basket 1, 2, and 3). Sometimes these columns have all the information and sometimes one or two of them are null. I have another column that I'm going to average these values into and save. Is there a sleek/easy way to get the average of these three columns even if one of them is null? Or do I have to have a special check for each one being null? Example data( ~~ is null) ``` - B1 - B2 - B3 - Avg ------------------------------ - 10 - 20 - 30 - 20 - 10 - ~~ - 30 - 20 - ~~ - 20 - ~~ - 20 ``` How would I write the T-SQL to update my temp table? ``` UPDATE @MyTable SET Avg = ??? ``` Answer: Thanks to Aaronaught for the method I used. I'm going to put my code here just in case someone else has the same thing. ``` WITH AverageView AS ( SELECT Results_Key AS xxx_Results_Key, AVG(AverageValue) AS xxx_Results_Average FROM @MyResults UNPIVOT (AverageValue FOR B IN (Results_Basket_1_Price, Results_Basket_2_Price, Results_Basket_3_Price)) AS UnpivotTable GROUP BY R…

Accepted Answer

Assuming you have some sort of ID column, the most effective way is probably to use `UNPIVOT` so you can use the normal row-based `AVG` operator (which ignores `NULL` values):

```
DECLARE @Tbl TABLE
(
    ID int,
    B1 int,
    B2 int,
    B3 int
)

INSERT @Tbl (ID, B1, B2, B3) VALUES (1, 10, 20, 30)
INSERT @Tbl (ID, B1, B2, B3) VALUES (2, 10, NULL, 30)
INSERT @Tbl (ID, B1, B2, B3) VALUES (3, 10, NULL, NULL)

SELECT ID, AVG(Value) AS Average
FROM @Tbl
UNPIVOT (Value FOR B IN (B1, B2, B3)) AS u
GROUP BY ID
```

If you don't have the ID column, you can generate a surrogate ID using `ROW_NUMBER`:

```
;WITH CTE AS
(
    SELECT
        B1, B2, B3,
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ID
    FROM @Tbl
)
SELECT ID, AVG(Value)
FROM CTE
UNPIVOT (Value FOR B IN (B1, B2, B3)) AS u
GROUP BY ID
```

MS-SQL Average Columns with NULL

Solution

Problem