title: Using outer query result in a subquery in postgresql
body: <p>I have two tables <code>points</code> and <code>contacts</code> and I'm trying to get the average <code>points.score</code> per contact grouped on a monthly basis.  <strong>Note</strong> that points and contacts aren't related, I just want the sum of points created in a month divided by the number of contacts that existed in that month.</p>
  
  <p>So, I need to sum points grouped by the created_at month, and I need to take the count of contacts FOR THAT MONTH ONLY.  It's that last part that's tricking me up.  I'm not sure how I can use a column from an outer query in the subquery.  I tried something like this:</p>
  
  <pre><code>SELECT SUM(score) AS points_sum,
    EXTRACT(month FROM created_at) AS month,
    date_trunc('MONTH', created_at) + INTERVAL '1 month' AS next_month,
    (SELECT COUNT(id) FROM contacts WHERE contacts.created_at &lt;= next_month) as contact_count
  FROM points
  GROUP BY month, next_month
  ORDER BY month
  </code></pre>
  
  <p>So, I'm extracting the actual month that my points are being summed, and at the same time, getting the beginning of the next_month so that I can say "Get me the count of contacts where their created at is &lt; next_month"</p>
  
  <p>But it complains that <code>column next_month doesn't exist</code>  This is understandable as the subquery knows nothing about the outer query.  Qualifying with <code>points.next_month</code> doesn't work either.</p>
  
  <p>So can someone point me in the right direction of how to achieve this?</p>
  
  <h2>Tables:</h2>
  
  <p><strong>Points</strong></p>
  
  <pre><code>score | created_at
  10    | "2011-11-15 21:44:00.363423"
  11    | "2011-10-15 21:44:00.69667" 
  12    | "2011-09-15 21:44:00.773289"
  13    | "2011-08-15 21:44:00.848838"
  14    | "2011-07-15 21:44:00.924152"
  </code></pre>
  
  <p><strong>Contacts</strong></p>
  
  <pre><code>id | created_at
  6  | "2011-07-15 21:43:17.534777"
  5  | "2011-08-15 21:43:17.520828"
  4  | "2011-09-15 21:43:17.506452"
  3  | "2011-10-15 21:43:17.491848"
  1  | "2011-11-15 21:42:54.759225"
  </code></pre>
  
  <p><strong>sum, month and next_month (without the subselect)</strong></p>
  
  <pre><code>sum | month | next_month
  14  | 7     | "2011-08-01 00:00:00"
  13  | 8     | "2011-09-01 00:00:00"
  12  | 9     | "2011-10-01 00:00:00"
  11  | 10    | "2011-11-01 00:00:00"
  10  | 11    | "2011-12-01 00:00:00"
  </code></pre>