locked
Calculate Weighted Average RRS feed

  • 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

Answers

  • 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 Vism 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 Vism 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

    mohammad waheed

    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

    mohammad waheed

    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


    mohammad waheed

    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 Vism 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

    mohammad waheed

    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 Vism 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 Vism Tuesday, August 14, 2018 9:50 AM
    Tuesday, August 14, 2018 9:49 AM