title: Hive: Sum over a specified group (HiveQL)
body: <p>I have a table:</p>
  
  <pre><code>key    product_code    cost
  1      UK              20
  1      US              10
  1      EU              5
  2      UK              3
  2      EU              6
  </code></pre>
  
  <p>I would like to find the sum of all products for each group of "key" and append to each row. For example for key = 1, find the sum of costs of all products (20+10+5=35) and then append result to all rows which correspond to the key = 1. So end result:</p>
  
  <pre><code>key    product_code    cost     total_costs
  1      UK              20       35
  1      US              10       35
  1      EU              5        35
  2      UK              3        9
  2      EU              6        9
  </code></pre>
  
  <p>I would prefer to do this without using a sub-join as this would be inefficient. My best idea would be to use the <code>over</code> function in conjunction with the <code>sum</code> function but I cant get it to work. My best try:</p>
  
  <pre><code>SELECT key, product_code, sum(costs) over(PARTITION BY key)
  FROM test
  GROUP BY key, product_code;
  </code></pre>
  
  <p>Iv had a look at the <a href="http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.0.0.2/ds_Hive/language_manual/ptf-window.html" rel="noreferrer">docs</a> but there so cryptic I have no idea how to work out how to do it. Im using Hive v0.12.0, HDP v2.0.6, HortonWorks Hadoop distribution.</p>