title: Combining two queries where one uses GROUP BY
body: <p>I have two tables. TABLE1 has columns:</p>
  
  <pre><code>pers_key
  cost
  visit
  </code></pre>
  
  <p>TABLE2 has columns:</p>
  
  <pre><code>pers_key
  months
  </code></pre>
  
  <p>First, I create a temporary table:</p>
  
  <pre><code>CREATE TABLE temp_table as
  SELECT pers_key,SUM(cost) AS sum_cost, COUNT(DISTINCT visit) AS visit_count
  FROM TABLE1
  GROUP BY pers_key;
  </code></pre>
  
  <p>Then, I create TABLE3:</p>
  
  <pre><code>CREATE TABLE TABLE3 as
  SELECT A.pers_key,
  B.sum_cost/A.months AS ind1,
  B.visit_count/A.months AS ind2
  FROM TABLE2 AS A, temp_table AS B
  WHERE A.pers_key = B.pers_key
  </code></pre>
  
  <p>I'm wondering if there is better way to achieve the same result here. Would it be possible to do this in one query without creating temp_table altogether? Perhaps something like:</p>
  
  <pre><code>CREATE TABLE TABLE3 as
  SELECT A.pers_key,
  (SUM(B.cost)over (partition by B.pers_key))/A.months AS ind1,
  (COUNT(B.visit)over (partition by B.pers_key))/A.months AS ind2
  FROM TABLE2 AS A, TABLE1 AS B
  WHERE A.pers_key = B.pers_key
  </code></pre>
  
  <p>Or is the temporary table required to achieve the desired result set?</p>