title: How to use a SQL window function to calculate a percentage of an aggregate
body: <p>I need to calculate percentages of various dimensions in a table. I'd like to simplify things by using window functions to calculate the denominator, however I am having an issue because the numerator has to be an aggregate as well.</p>
  
  <p>As a simple example, take the following table:</p>
  
  <pre><code>create temp table test (d1 text, d2 text, v numeric);
  insert into test values ('a','x',5), ('a','y',5), ('a','y',10), ('b','x',20);
  </code></pre>
  
  <p>If I just want to calculate the share of each individual row out of d1, then windowing functions work fine:</p>
  
  <pre><code>select d1, d2, v/sum(v) over (partition by d1)
  from test;
  
  "b";"x";1.00
  "a";"x";0.25
  "a";"y";0.25
  "a";"y";0.50
  </code></pre>
  
  <p>However, what I need to do is calculate the overall share for the sum of d2 out of d1. The output I am looking for is this:</p>
  
  <pre><code>"b";"x";1.00
  "a";"x";0.25
  "a";"y";0.75
  </code></pre>
  
  <p>So I try this:</p>
  
  <pre><code>select d1, d2, sum(v)/sum(v) over (partition by d1)
  from test
  group by d1, d2;
  </code></pre>
  
  <p>However, now I get an error:</p>
  
  <pre><code>ERROR:  column "test.v" must appear in the GROUP BY clause or be used in an aggregate function
  </code></pre>
  
  <p>I'm assuming this is because it is complaining that the window function is not accounted for in the grouping clause, however windowing functions cannot be put in the grouping clause anyway.</p>
  
  <p>This is using Greenplum 4.1, which is a fork of Postgresql 8.4 and shares the same windowing functions. Note that Greenplum cannot do correlated subqueries.</p>