CODEWITHSUNDEEP
sqlsql-server
Vinjeru BH
Vinjeru BH

Reputation: 47

How do I calculate group subtotals for a specific number of rows in a table

I am really learning a lot here. Thanks to all the support. I have another challenge however.

I want to calculate a weighted Index for a group of rows 'Div' within a particular month as shown below:

Wght   tMonth tYear Div  Indices

37.5     01   2015   01    157.27
2.7      01   2015   01    127.36
58.7     01   2015   01    142.48
     DivIndex              146.11
34.9     01   2015   02    133.33
6.7      01   2015   02    136.49
52.4     01   2015   02    131.34
     DivIndex              124.43
43.9     02   2015   01    157.18
44.8     02   2015   01    127.42
     DivIndex              126.09
58.7     02   2015   03    145.67
7.5      02   2015   03    134.70
6.7      02   2015   03    137.24
     DivIndex              104.72
54.0     03   2015   05    160.61
     DivIndex               86.73
48.1     03   2015   04    127.49
58.7     03   2015   04    148.62
     DivIndex              148.58

I used Excel to compute the 'DivIndex' and that is what I want to come up with in Sql Server 2008R2.

Thanks in advance for any help.

Upvotes: 1

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do the calculation as:

select tyear, tmonth,
       sum(weight * dev_indices) / sum(weight)
from t
group by tyear, tmonth
order by tyear, tmonth;

Upvotes: 1

Related Questions