How to find the average of multiple columns with different specific weights?

Question

I need to calculate the average of Value_A, Value_B, and Value_C in mssql.
My problem is that every information I need is in one row.

Every value has its own weight:

(sum of values * weight) / (sum weight):

Every column can be null. If there is a value but not a weight, weight is 100,
if there is a weight and no value then the specific value is not considered of course

e.g.

1st column:

(2*100+1*80)/(100+80)= 2.55  ≈ 2.6

2nd column:

(1*100+2*80)/(100+80)

+------+---------+---------+---------+----------+----------+----------+-----+
| ID   | VALUE_A | VALUE_B | VALUE_C | Weight_A | Weight_B | Weight_C | AVG |
+------+---------+---------+---------+----------+----------+----------+-----+
| 1111 | 2       | 1       | null    | 100      | 80       | 60       | 2.6 |
+------+---------+---------+---------+----------+----------+----------+-----+
| 2222 | 1       | 2       | null    | 100      | 80       | 60       |     |
+------+---------+---------+---------+----------+----------+----------+-----+

I got this far to get the AVG values without weights

select ID, VALUE_A, VALUE_B, VALUE_C, Weight_A, Weight_B, Weight_C,
        (SELECT AVG(Cast(c as decimal(18,1)))
        FROM   (VALUES(VALUE_A),
                      (VALUE_B),
                      (VALUE_C)) T (c)) AS [Average]
FROM table

Second try was selecting sum of values multiply them by their weights and then divide them by sum of the weights. Sum of weights is missing. Can't figure out how to add it

select *, 
    (SELECT SUM(Cast(c as decimal(18,1)))
    FROM   (VALUES(VALUE_A* ISNULL(Weight_A,100)),
                  (VALUE_B* ISNULL(Weight_B,100)),
                  (VALUE_C* ISNULL(Weight_C,100))

    ) T (c)) AS [Average]

FROM table

Gordon Linoff · Answer 1 · 2015-05-18T16:32:16.600

2

Is this what you are looking for?

SELECT SUM(val * COALESCE(w, 100)) / SUM(w) as weighted_average,
       SUM(val * COALESCE(w, 100)) as weighted_sum
FROM table t CROSS APPLY
     (VALUES (t.VALUE_A, t.Weight_A),
             (t.VALUE_B, t.Weight_B),
             (t.VALUE_C, t.Weight_C)
     ) a(val, w)
WHERE a.val IS NOT NULL;

edited May 18 '15 at 16:32

answered May 18 '15 at 10:58

Gordon Linoff

1,242,037
58
646
786

Does this exclude the weight from `SUM(w)` if the Val is null? looking at the second EG above `(1*100+2*80)/(100+80)` it looks like OP wants that behavior. – Daniel E. May 18 '15 at 12:25
1

@DanielE. . . . Yes, I see that in the question now. This is readily handled with a `WHERE` clause. – Gordon Linoff May 18 '15 at 16:32

Daniel E. · Answer 2 · 2015-05-18T11:20:04.280

This is how Average could be calculated

    SELECT *
        ,CASE 
            WHEN (W.weight_A + W.Weight_B+ W.Weight_C) = 0
                THEN 0
            ELSE (ISNULL(VALUE_A, 0 * W.Weight_A)
                  + (ISNULL(VALUE_B, 0) * W.Weight_B) 
                  + (ISNULL(VALUE_C, 0) * W.Weight_C)) 
                  / (W.weight_A + w.Weight_B+ W.Weight_C)
            END Average
FROM TABLE t
CROSS APPLY (Select CASE WHEN VALUE_A is null then 0 ELSE ISNULL(Weight_A,100) END [Weight_A]
            ,CASE WHEN VALUE_B is null then 0 ELSE ISNULL(Weight_B,100) END [Weight_B]
            ,CASE WHEN VALUE_C is null then 0 ELSE ISNULL(Weight_C,100) END [Weight_C]) W

How to find the average of multiple columns with different specific weights?

2 Answers2