title: SQL SUMPRODUCT with a twist
body: <pre><code>Table WIDGET
      Columns Country, Contract, Price
  
  Table WEIGHT
      Columns Contract, Weight
  </code></pre>
  
  <p>I am trying to do <code>SUMRPRODUCT(Contract, Weight)/SUM(Weight)</code> but for all values of <code>Country</code>. What I have tried is adapted from <a href="https://stackoverflow.com/questions/1698408/weighted-average-in-t-sql-like-excels-sumproduct">Weighted average in T-SQL (like Excel&#39;s SUMPRODUCT)</a> and looks like</p>
  
  <pre><code>SELECT 
      Country 
      SUM(widget.price * weight.weight) / SUM(weight.weight)
  FROM
      Widget
          INNER JOIN
      Weight ON Widget.contract = Weight.contract
  WHERE
      Weight.contract &gt;= '2016-01-01'
      AND Weight.contract &lt;= '2016-12-01'
  </code></pre>
  
  <p>The problem with this is that it calculates it for one value of <code>Country</code> but not for all of them. How can I get a list of <code>DISTINCT(Country)</code> and the <code>SUMPRODUCT()/SUM()</code> for the filtered contracts next to it?</p>
  
  <p>Sample Data</p>
  
  <pre><code>+-----------+---------+------------+-------+
  | widget_id | country | contract   | price |
  +-----------+---------+------------+-------+
  |         4 | CA      | 2016-01-01 | 16.00 |
  |         5 | CA      | 2016-02-01 | 32.00 |
  |         6 | CA      | 2016-03-01 | 64.00 |
  |         1 | US      | 2016-01-01 | 32.00 |
  |         2 | US      | 2016-02-01 | 64.00 |
  |         3 | US      | 2016-03-01 | 96.00 |
  +-----------+---------+------------+-------+
  
  +-----------+------------+--------+
  | weight_id | contract   | weight |
  +-----------+------------+--------+
  |         1 | 2016-01-01 |      1 |
  |         2 | 2016-02-01 |      8 |
  |         3 | 2016-03-01 |     64 |
  +-----------+------------+--------+
  </code></pre>
  
  <p>Desired output</p>
  
  <pre><code>+---------+-----------+
  | Country | Wtd Price |
  +---------+-----------+
  | CA      | 59.835616 |
  | US      | 91.616438 |
  +---------+-----------+
  </code></pre>