Dynamic horizontal aggregate (AVG)

I have a table which contains 3 float(nullable) columns:

Value1  ||  Value2  ||  Value3 
NULL        NULL        100.00
10.00       20.00       NULL
10.00       10.00       100.00

I need to get the average from these field (value1+value2+value3 / 3), but the divider is actually how many of these field that has value. So the expected result would be :

Value1  ||  Value2  ||  Value3 || AVG
NULL        NULL        100.00    100.00 (from 100/1)
10.00       20.00       NULL      15.00  (from 30/2)
10.00       10.00       100.00    40.00  (from 120/3)

Anyone can help?

I’ve tried a solution according to this thread : Average of multiple columns

But didn’t seem to work.

I’ve also tried to handle this in C#, in which i use for loop and a counter to determine the divider, and then divide them. It actually works, but i prefer to handle this via query, because it took too much time when the records went over 3000 row(s).

My current query:

SELECT *,
       (SELECT AVG(c)
        FROM   (VALUES(Value1),
                      (Value2),
                      (Value3)) T (c)) AS [Average]
FROM   tbl_trans_score

Thanks in advance.

Answer

You can try this below script-

SELECT
CASE 
    WHEN (COALESCE(Value1,0)+COALESCE(Value2,0)+COALESCE(Value3,0)) = 0 THEN 0 
    ELSE
    (
        COALESCE(Value1,0)+
        COALESCE(Value2,0)+
        COALESCE(Value3,0)
    )/
    (
        CASE WHEN Value1 IS NULL THEN 0 ELSE 1 END+ 
        CASE WHEN Value2 IS NULL THEN 0 ELSE 1 END+ 
        CASE WHEN Value3 IS NULL THEN 0 ELSE 1 END 
    )
END
FROM your_table

Advertisement

Answer