title: aggregate functions are not allowed in WHERE - when joining PostgreSQL tables
body: <p>In a game using PostgreSQL 9.3.10 some players have paid for a "VIP status", which is indicated by <strong>vip</strong> column containing a date from future:</p>
  
  <pre><code># \d pref_users
  
     Column   |            Type             |     Modifiers      
  ------------+-----------------------------+--------------------
   id         | character varying(32)       | not null
   first_name | character varying(64)       | not null
   last_name  | character varying(64)       | 
   vip        | timestamp without time zone | 
  </code></pre>
  
  <p>Also players can rate other players by setting <strong>nice</strong> column to <em>true</em>, <em>false</em> or leaving it at <em>null</em>:</p>
  
  <pre><code> # \d pref_rep
  
    Column   |            Type             |                         Modifiers                         
  -----------+-----------------------------+-----------------------------------------------------------
   id        | character varying(32)       | not null
   author    | character varying(32)       | not null
   nice      | boolean                     | 
  </code></pre>
  
  <p>I calculate a "reputation" of VIP-players by issuing this SQL JOIN statement:</p>
  
  <pre><code># select u.id, u.first_name, u.last_name, 
    count(nullif(r.nice, false))-count(nullif(r.nice, true)) as rep 
    from pref_users u, pref_rep r 
    where u.vip&gt;now()and u.id=r.id group by u.id order by rep asc;
  
  
             id            |           first_name           | last_name | rep  
  -------------------------+--------------------------------+--------------------
   OK413274501330          | ali                            | salimov   | -193
   OK357353924092          | viktor                         | litovka   | -137
   DE20287                 | sergej warapow                 |              
  </code></pre>
  
  <p>My question is please the following:</p>
  
  <p><strong>How to find all negatively rated players, who have rated other players?</strong>   </p>
  
  <p>(The background is that I have added a possibility to rate others - to all VIP-players. Until that only positively rated players could rate others).</p>
  
  <p>I have tried the following, but get the error below:</p>
  
  <pre><code># select count(*) from pref_rep r, pref_users u 
  where r.author = u.id and u.vip &gt; now() and 
  u.id in (select id from pref_rep 
  where (count(nullif(nice, false)) -count(nullif(nice, true))) &lt; 0);
  
  ERROR:  aggregate functions are not allowed in WHERE
  LINE 1: ...now() and u.id in (select id from pref_rep where (count(null...
                                                               ^
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>I am trying it with temporary table now -</p>
  
  <p>First I fill it with all negatively rated VIP-users and this works well:</p>
  
  <pre><code># create temp table my_temp as select u.id, u.first_name, u.last_name,
    count(nullif(r.nice, false))-count(nullif(r.nice, true)) as rep 
    from pref_users u, pref_rep r 
    where u.vip&gt;now() and u.id=r.id group by u.id;
  
   SELECT 362
  </code></pre>
  
  <p>But then my SQL JOIN returns too many identical rows and I can not find what condition is missing there:</p>
  
  <pre><code> # select u.id, u.first_name, u.last_name 
     from pref_rep r, pref_users u, my_temp t 
     where r.author=u.id and u.vip&gt;now() 
     and u.id=t.id and t.rep&lt;0;
  
             id            |           first_name           |         last_name          
  -------------------------+--------------------------------+----------------------------
   OK400153108439          | Vladimir                       | Pelix
   OK123283032465          | Edik                           | Lehtik
   OK123283032465          | Edik                           | Lehtik
   OK123283032465          | Edik                           | Lehtik
   OK123283032465          | Edik                           | Lehtik
   OK123283032465          | Edik                           | Lehtik
   OK123283032465          | Edik                           | Lehtik
  </code></pre>
  
  <p>Same problem (multiple rows with same data) I get for the statement:</p>
  
  <pre><code># select u.id, u.first_name, u.last_name 
    from pref_rep r, pref_users u 
    where r.author = u.id and u.vip&gt;now() 
    and u.id in (select id from my_temp where rep &lt; 0);
  </code></pre>
  
  <p>I wonder what condition could be missing here?</p>