Calculated Weighted Average in SQL

Question

I am trying to calculate the YTD value for every month. How could I achieve this in SQL Server?

Green is how my data looks in the table.I am suppose to calculate the one in Yellow. Your help will be much appreciated.

Year    Month   Monthly_Score   N_size  YTD_Score
2017    1        70             10       70*10/10
2017    2        80             20       70*10/(10+20) + 80 *20 /(10+20)
2017    3        90             30       70*10/(10+20+30) + 80 * 20 /(10+20+30) 
                                         +90*30/(10+20+30)

which version of sql server are you using? – Vamsi Prabhala May 22 '17 at 21:36 — Vamsi Prabhala, May 22 '17 at 21:36

Vamsi Prabhala · Accepted Answer · 2017-06-23T21:12:48.750

3

You can use the window function SUM if your version of SQL Server supports it.

select year,month,monthly_score,n_size,
1.0*sum(monthly_score*n_size) over(partition by year order by month)/
sum(n_size) over(partition by year order by month)
from yourtable

Note that the value resets every year.

Edit: For SQL Server versions below 2012 that support outer apply.

select y.year,y.month,y.monthly_score,y.n_size,t.ytd_score
from yourtable y
outer apply (select 1.0*sum(monthly_score*n_size)/sum(n_size) as ytd_score 
             from yourtable y1 
             where y.year=y1.year and y1.month<=y.month
            ) t

edited Jun 23 '17 at 21:12

answered May 22 '17 at 21:37

Vamsi Prabhala

48,685
4
36
58

That's awesome! I think it should work. I just tried. I am going to test for others and let you know. – Swethak May 22 '17 at 21:40
Thank you so much! I have been trying this since couple of hours now. Appreciate it. – Swethak May 22 '17 at 21:48
If this has to do in SQL Server 2008, how can this be done? please help! – Swethak Jun 23 '17 at 21:03
I tried implementing your query using my tables, but the results are not the same .(Compared with excel). Anything you think that I am missing? – Swethak Jun 23 '17 at 21:34

score 1 · Answer 2 · answered May 22 '17 at 21:41

1

You can use the window variant of sum to sum to calculate running totals:

SELECT [Year], [Month], [Monthly_Score], [N_Size],
       SUM([Monthly_Score] * [N_Size]) 
          OVER (ORDER BY [Year], [Month]
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / 
      SUM([N_Size]) 
          OVER (ORDER BY [Year], [Month]
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTD_Score
FROM  my_table

answered May 22 '17 at 21:41

Mureinik

297,002
52
306
350

Thanks so much for your time. I just tried something similar but without UNBOUNDED PRECEDING and CURRENT ROW and it worked. Just curious what difference does this make? – Swethak May 22 '17 at 21:50
If this has to be done in SQL Server 2008. How can this be achieved? – Swethak Jun 23 '17 at 21:04

Calculated Weighted Average in SQL

2 Answers2