title: SQL Server - Query to calculate weighted average based on last values for each year
body: <p>Let's say I have the following columns from a SQL table for Major League Baseball pitchers:</p>
  
  <pre><code>player_id   game_id             season_ip | season_whip |career_ip
  </code></pre>
  
  <p>And we have the following data:</p>
  
  <pre><code>502190  2013/04/18/miamlb-cinmlb-1  19      1.32    504
  502190  2013/04/19/miamlb-cinmlb-1  19      1.32    504
  502190  2013/06/11/cinmlb-chnmlb-1  73      1.32    558
  502190  2013/06/13/cinmlb-chnmlb-1  81      1.24    566
  502190  2013/09/29/pitmlb-cinmlb-1  192     1.25    677
  502190  2014/04/22/cinmlb-pitmlb-1  28      0.99    705
  502190  2014/05/26/cinmlb-lanmlb-1  71      1.06    748
  502190  2014/09/28/pitmlb-cinmlb-1  214     1.25    891
  502190  2015/03/25/texmlb-cinmlb-1  14      0.71    891
  502190  2015/08/15/wasmlb-sfnmlb-1  143     1.17    1034
  
  453286  2013/05/05/detmlb-houmlb-1  39      1.04    844
  453286  2013/05/16/detmlb-texmlb-1  54      0.99    859
  453286  2013/09/29/detmlb-miamlb-1  214     0.97    1019
  453286  2014/06/18/kcamlb-detmlb-1  98      1.25    1117
  453286  2014/07/15/nasmlb-aasmlb-1  126     1.17    1145
  453286  2014/09/28/minmlb-detmlb-1  220     1.18    1239
  453286  2015/03/22/wasmlb-detmlb-1  14      0.93    1239
  453286  2015/08/15/wasmlb-sfnmlb-1  165     0.9     1404
  </code></pre>
  
  <p>I need a SQL query to get the weighted average of the <code>season_whip</code> for each <code>player_id</code> based on the <code>season_ip</code> (innings pitched).<br>
  I only want to use the last entry for each year (note that the <code>game_id</code> column contains the year). So for <code>player_id</code> 502190, the last entry for the year 2015 is <code>game_id</code> <code>2015/08/15/wasmlb-sfnmlb-1</code>, year 2014 is <code>game_id</code> <code>2014/09/28/pitmlb-cinmlb-1</code>.  </p>
  
  <p>The formula is for weighted average is:</p>
  
  <pre><code>sum(season_ip * season_whip) / sum(season_ip)   
  </code></pre>
  
  <p>So, for example, <code>player_id</code> 502190, his whip is </p>
  
  <pre><code>[192 * 1.25 + 214 * 1.25 + 143 * 1.17] / [192 + 214 + 143] = 1.229
  </code></pre>
  
  <p>I included <code>career_ip</code> because it's the only value that will increase over the span of all the years.  The <code>season_ip</code> resets each year and the <code>season_whip</code> varies throughout the year.</p>
  
  <p>Any help is appreciated.  I'm using SQL Server 2014. </p>