title: PostgreSQL - Referencing another aggregate column in a window function
body: <p>Here's the query:</p>
  
  <pre><code>SELECT name,
         group_name,
         number1, -- associated with either the name or the group_name
         number2,
         ...
         (number1 * number2) - SUM(number3) AS total_difference,
         SUM(total_difference) OVER (PARTITION BY group_name) AS grand_total
  FROM t
  GROUP BY name,
           group_name,
           number1,
           number2,
           ...;
  </code></pre>
  
  <p>A single group_name may have many associated names.</p>
  
  <p>total_difference is generated for each name. My goal is for grand_total to be the sum of the total_difference for every name in group_name.  total_difference is calculated for each name using number1 through numberx, some of which are related to the name and some of which are related to the group_name. Here's an example table (ignore the math, these numbers are clearly just made up):</p>
  
  <pre><code>name | group_name | number1 | number2 | number3 | total_difference | grand_total
  -----+------------+---------+---------+---------+------------------+-------------
  Fred | A          | 3       | 67      | 2       | 10               | 30
  Amy  | A          | 5       | 25      | 45      | 20               | 30
  Jim  | B          | 8       | 33      | 15      | 30               | 100
  Tom  | B          | 2       | 6       | 35      | 45               | 100
  Al   | B          | 6       | 89      | 4       | 25               | 100
  ...
  </code></pre>
  
  <p>The problem is that I clearly can't reference total_difference when I create grand_total, because it doesn't exist yet. I also can't just replicate the math:</p>
  
  <pre><code>SELECT ...
         SUM((number1 * number2) - SUM(number3)) OVER (PARTITION BY group_name) AS grand_total
  ...
  </code></pre>
  
  <p>because then I would have two difference aggregate functions with different group by arguments (I tried nesting window functions, but I knew that was a dumb idea even before I got the error message).</p>
  
  <p>I recognize that I could just make the table and perform a join afterwards, and I have absolutely no problem with doing that.  However, it bothers me that I can't see this... This is more a learning experience than anything.</p>
  
  <p>How can I pull off what I'm going for.  Do I even need a window function?  Is this even possible?</p>
  
  <p>This table is solely for presentation purposes, FYI.  I'm using PostgreSQL 9.3.</p>