title: SQL to get total outstanding balance meeting withdrawal criteria
body: <p>I have a payments table that has a user_id and an amount. Users have a minimum withdrawal of $5.00, and they can have any number of payments to them. Their total balance isn't stored anywhere (to keep it simple and avoid synchronization issues), instead it's found by doing a query like:</p>
  <pre><code>SELECT SUM(amount) 
    FROM payment 
   WHERE user_id = 5;
  </code></pre>
  <p>I want to find out the grand total of all balances that meet the withdrawal requirement. What I wish I could do is something like:</p>
  <pre><code>  SELECT SUM(SUM(amount)) 
      FROM payment 
     WHERE sum(amount) &gt;= 5.00
  GROUP BY user_id;
  </code></pre>
  <p>Unfortunately I get the error</p>
  <blockquote>
  <p>ERROR:  aggregates not allowed in WHERE clause</p>
  </blockquote>
  <p>How can I write this query? I'm using postgres 8.4.</p>
  <h3>Part 2:</h3>
  <p>I'm not sure if this is even possible, but this payments table also includes payments made <em>by</em> the user: the payment table includes amount, from_user_id, and to_user_id. It's a single-entry accounting system. It would be great if I could also subtract the payments the user has made. The pseudo-sql is:</p>
  <pre><code>SELECT (sum of all amounts) 
   WHERE (sum(amount where to_user_id = 5) - sum(amount where from_user_id = 5)) &gt; 5.00
  </code></pre>