title: Weighted average in T-SQL (like Excel's SUMPRODUCT)
body: <p>I am looking for a way to derive a weighted average from two rows of data with the same number of columns, where the average is as follows (borrowing Excel notation):</p>
  
  <pre><code>(A1*B1)+(A2*B2)+...+(An*Bn)/SUM(A1:An)
  </code></pre>
  
  <p>The first part reflects the same functionality as Excel's SUMPRODUCT() function.  </p>
  
  <p>My catch is that I need to dynamically specify which row gets averaged with weights, and which row the weights come from, and a date range.  </p>
  
  <p>EDIT:  This is easier than I thought, because Excel was making me think I required some kind of pivot.  My solution so far is thus:</p>
  
  <pre><code>select sum(baseSeries.Actual * weightSeries.Actual) / sum(weightSeries.Actual)
  from (
      select RecordDate , Actual 
      from CalcProductionRecords 
      where KPI = 'Weighty'
  ) baseSeries inner join (       
      select RecordDate , Actual 
      from CalcProductionRecords 
      where KPI = 'Tons Milled'   
  ) weightSeries on baseSeries.RecordDate = weightSeries.RecordDate
  </code></pre>