# Calculate Weighted Average • ### Question

• Hi

I have to claculate weighted average. What I want to achieve here is wherever Score is NULL I want to calculate weighted average and want to display in place of null

Formula to claculate is :

Calculate percent weight for that parent standard ID. For Ex: Stndard ID = 107 has score null so the weight with parent standard ID = 107 has to be calculated like, weight/SUM(weight)for two rows.  Now multiply each weight with child score for two rows with parentstandardID 107 and SUM Them.

CREATE TABLE mytable(
StandardID       VARCHAR(30) NOT NULL
,Standard         VARCHAR(1)    NOT NULL
,Score            INTEGER
,parentstandardid INTEGER
,weight           INT

);
INSERT INTO mytable(StandardID,Standard,Score,parentstandardid,weight ) VALUES
( 1,'A',3,0,1)
,( 107,'B',NULL,'1',1)
,( 111,'C',1,'107',1)
,( 112,'D',2,'107',1);

Select * from mytable

Select
m.StandardID,
m.[Standard],
m.Score,
(CASE WHEN m.Score IS NULL THEN t.PercentScore ELSE m.Score END) AS NewScore,
m.parentstandardid,
m.weight
from  mytable m
left join (Select parentstandardid ,   SUM(score *  (weight * SUM(Weight) ))  AS PercentScore
from  mytable Group By parentstandardid) t
on m.StandardID=t.parentstandardid

Many Thanks !

Monday, August 13, 2018 6:58 PM

• Hi  Vism,

As you said that 'For Ex: Stndard ID = 107 has score null so the weight with parent standard ID = 107 has to be calculated like, weight/SUM(weight)for two rows.  Now multiply each weight with child score for two rows with parentstandardID 107 and SUM Them.',did you mean that NewScore=1<the weight of 107>/(1<the weight of 111>+1<the weight of 111>)*(1<the Score of 111>+2<the Score of 112>) when the StandardID=107 ?

If so, the result '0.75' may be right.

If I have an incorrect understanding, please let me know.

Hope it is helpful to you.

Best Regards,

Rachel Wang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Tuesday, August 14, 2018 9:48 AM
Tuesday, August 14, 2018 9:31 AM
• Thanks Rachel

I used this formula in my Left Join

SUM(CAST(Score AS decimal) * CAST([Weight] AS decimal))/ SUM(CAST([Weight] AS decimal)) AS ChildScore, this too calculated the weighted Average.

Many Thanks for your help :)

• Marked as answer by Tuesday, August 14, 2018 9:50 AM
Tuesday, August 14, 2018 9:49 AM

### All replies

• What should be the value in place of NULL value

Monday, August 13, 2018 7:35 PM
• weight/sum(weight) * score, need to calculate it from two rows with parentId 107 and than sum these values from two rows and display in place of null where standard1d is 7. Need to make it dynamic as in real time it can be weighted average of any no of rows. thanks!
Monday, August 13, 2018 7:55 PM
• Select
m.StandardID,
m.[Standard],
m.Score,
(CASE WHEN m.Score IS NULL THEN Isnull(t.ScoreSUM,0) *  (weight * t.WeightSum )  ELSE m.Score END) AS NewScore,
m.parentstandardid,
m.weight
from  mytable m
left join (Select parentstandardid ,   SUM(score) As ScoreSUM, SUM(Weight) As WeightSum
from  mytable Group By parentstandardid) t
on m.StandardID=t.parentstandardid

Monday, August 13, 2018 8:12 PM
• In CASE statement multiplying weight with t.weightSum is no5 correct, here we are multiplying it with weight of standard ID 107 where as we need to calculate every thing for parent standard Ids 107
Monday, August 13, 2018 8:41 PM
• SUM(score *  (weight * SUM(Weight) ))  AS PercentScore

In this above query the first "weight"  is from which StandardID

Monday, August 13, 2018 9:03 PM
• I need to calculate weight only for parentstandardid 107 It’s like

Score if no children. Parentscore Childscore Percent. Weight
Childscore*percent Weight/(SUM(weight)
1.5. 0.50 1 0.50. 1
1.00. 2. 0.50. 1
SUM. SUM= 1.50 SUM =1.00 So need to display 1.5 in
place of NULL and standardID107 Thanks !
• Edited by Monday, August 13, 2018 9:23 PM
Monday, August 13, 2018 9:16 PM
• Trying to display in form of table but not able to do so
Monday, August 13, 2018 9:24 PM
• ;With CTE as (Select parentstandardid,sum( weight) weight from  mytable group by parentstandardid)
Select
m.StandardID,
m.[Standard],
m.Score,
(CASE WHEN m.Score IS NULL THEN t.PercentScore ELSE m.Score END) AS NewScore,
m.parentstandardid,
m.weight
from  mytable m
left join (

Select mt.parentstandardid ,   sum(score *( mt.weight *CTE.weight)) AS PercentScore
from  mytable mt
Inner join CTE on CTE.parentstandardid=mt.parentstandardid
Group By mt.parentstandardid
) t
on m.StandardID=t.parentstandardid

Monday, August 13, 2018 9:31 PM
• ```DECLARE @MyTable TABLE (
StandardID       VARCHAR(30) NOT NULL
,Standard         VARCHAR(1) NOT NULL
,Score            INT
,ParentStandardID INT
,Weight           INT
);

INSERT INTO @MyTable VALUES
( 1,'A',3,0,1)
,( 107,'B',NULL,'1',1)
,( 111,'C',1,'107',1)
,( 112,'D',2,'107',1);

WITH CTE_1 AS (
SELECT ParentStandardID, SUM(Weight) AS SumWeightByParentStandardID
FROM @MyTable
GROUP BY ParentStandardID
),
CTE_2 AS (
SELECT t.ParentStandardID, t.Score * (t.Weight * c.SumWeightByParentStandardID) AS PercentScore
FROM @MyTable AS t
INNER JOIN CTE_1  AS c ON c.ParentStandardID = t.ParentStandardID
),
CTE_3 AS (
SELECT ParentStandardID, SUM(PercentScore) AS PercentScore
FROM CTE_2
GROUP BY ParentStandardID
)

SELECT
c.StandardID,
c.Standard,
c.Score,
CASE WHEN c.Score IS NULL THEN p.PercentScore ELSE c.Weight END AS NewScore,
c.ParentStandardID,
c.Weight
FROM @MyTable AS c
LEFT JOIN CTE_3 AS p ON p.ParentStandardID = c.StandardID;```

A Fan of SSIS, SSRS and SSAS

Monday, August 13, 2018 9:36 PM
• Many Thanks! But this gives NewScore 6, whereas in this scenario NewScore should be 1.50
Monday, August 13, 2018 10:15 PM
• If I get weight as comma delimited string, how can I apply this function number1/SUM(Allnumbers) + number2/SUM(Allnumbers). This way I might be able to achieve the correct result.

Select
m.StandardID,
m.Standard,
m.Score,
(CASE WHEN m.Score IS NULL THEN 0 ELSE m.Score END) AS NewScore,
t.weight as weight,
m.parentstandardid,
m.weight
from  mytable m
left join (Select parentstandardid , GROUP_CONCAT(weight) AS Weight, SUM(Score) AS PercentScore
from  mytable Group By parentstandardid) t
on m.StandardID=t.parentstandardid

Monday, August 13, 2018 10:29 PM
• this will also not help
Monday, August 13, 2018 10:43 PM
• Hi  Vism,

According to your description , you would like to get the correct score by percentage in place of NULL and the values which  are not NULL are changeless. Right ?

If so , you can try following script.

```CREATE TABLE mytable(
StandardID       VARCHAR(30) NOT NULL
,Standard         VARCHAR(1)    NOT NULL
,Score            INTEGER
,parentstandardid INTEGER
,weight           INT

);
INSERT INTO mytable(StandardID,Standard,Score,parentstandardid,weight ) VALUES
( 1,'A',3,0,1)
,( 107,'B',NULL,'1',1)
,( 111,'C',1,'107',1)
,( 112,'D',2,'107',1);

;with cte as
(select a.StandardID, sum (b.weight) sum_weight, sum (b.Score)  sum_Score
from mytable a
left join mytable b
on  a.StandardID=b.parentstandardid
group by a.StandardID)

select a.StandardID,a.Standard,a.Score,
case when a.Score is null then Convert(decimal(18,2),(1.*a.weight)/c.sum_weight*c.sum_Score)
else  a.Score end  as NewScore,
a.parentstandardid,a.weight
from cte c join mytable a on c.StandardID=a.StandardID
/*
StandardID                     Standard Score       NewScore                                parentstandardid weight
------------------------------ -------- ----------- --------------------------------------- ---------------- -----------
1                              A        3           3.00                                    0                1
107                            B        NULL        1.50                                    1                1
111                            C        1           1.00                                    107              1
112                            D        2           2.00                                    107              1
*/```

Hope it is helpful to you.

Best Regards,

Rachel Wang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, August 14, 2018 6:26 AM
• Hi Rachel, thanks for the reply. What does 1.*a.weight do?
Tuesday, August 14, 2018 6:59 AM
• Hi  Vism,

You can see the differences in following example. '1.*a.weight ' is to make the value as a decimal to calculate .

```;with cte as
(select a.StandardID, sum (b.weight) sum_weight, sum (b.Score)  sum_Score
from mytable a
left join mytable b
on  a.StandardID=b.parentstandardid
group by a.StandardID)

select
case when a.Score is null then a.weight/c.sum_weight*c.sum_Score
else  a.Score end  as NewScore
from cte c join mytable a on c.StandardID=a.StandardID
/*
NewScore
-----------
3
0
1
2
*/

;with cte as
(select a.StandardID, sum (b.weight) sum_weight, sum (b.Score)  sum_Score
from mytable a
left join mytable b
on  a.StandardID=b.parentstandardid
group by a.StandardID)

select
case when a.Score is null then Convert(decimal(18,2),(1.*a.weight)/c.sum_weight*c.sum_Score)
else  a.Score end  as NewScore
from cte c join mytable a on c.StandardID=a.StandardID
/*
NewScore
---------------------------------------
3.00
1.50
1.00
2.00
*/

```

Hope it is helpful to you.

Best Regards,

Rachel Wang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Tuesday, August 14, 2018 7:12 AM
• It gives score correct in this case but not showing good for other vlaues.

CREATE TABLE mytable(
StandardID       VARCHAR(30) NOT NULL
,Standard         VARCHAR(1)    NOT NULL
,Score            INTEGER
,parentstandardid INTEGER
,weight           INT

);
INSERT INTO mytable(StandardID,Standard,Score,parentstandardid,weight ) VALUES
( 1,  'A',3,0, 1)
,( 107,'B',NULL,'1',1)
,( 111,'C',3,'107',1)
,( 112,'D',3,'107',1)
,( 113,'E',NULL,'107',1)
,( 114,'F',NULL,'107',1)
,( 115,'G',NULL,'107',1)
,( 116,'H',NULL,'107',1)
,( 117,'I',NULL,'107',1)
,( 118,'J',NULL,'107',1)
,( 119,'K',NULL,'111',1)
,( 120,'L',NULL,'111',1)

For these New Score should be 1, whereas this formula gives 0.75.

Thanks!

Tuesday, August 14, 2018 8:03 AM
• Hi  Vism,

As you said that 'For Ex: Stndard ID = 107 has score null so the weight with parent standard ID = 107 has to be calculated like, weight/SUM(weight)for two rows.  Now multiply each weight with child score for two rows with parentstandardID 107 and SUM Them.',did you mean that NewScore=1<the weight of 107>/(1<the weight of 111>+1<the weight of 111>)*(1<the Score of 111>+2<the Score of 112>) when the StandardID=107 ?

If so, the result '0.75' may be right.

If I have an incorrect understanding, please let me know.

Hope it is helpful to you.

Best Regards,

Rachel Wang

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Tuesday, August 14, 2018 9:48 AM
Tuesday, August 14, 2018 9:31 AM
• Thanks Rachel

I used this formula in my Left Join

SUM(CAST(Score AS decimal) * CAST([Weight] AS decimal))/ SUM(CAST([Weight] AS decimal)) AS ChildScore, this too calculated the weighted Average.

Many Thanks for your help :)

• Marked as answer by Tuesday, August 14, 2018 9:50 AM
Tuesday, August 14, 2018 9:49 AM