PostgreSQL: having clause not working as expected

I have a user DE9013 with two positive ratings in an SQL-table:

# select * from pref_rep where id='DE9013';
   id   | author | good | fair | nice | about |         last_rated         |   author_ip
--------+--------+------+------+------+-------+----------------------------+---------------
 DE9013 | DE9241 | t    | t    | t    |       | 2011-03-06 09:23:00.400518 | 97.33.154.43
 DE9013 | DE9544 | t    | t    | t    |       | 2011-03-06 10:06:37.561277 | 97.33.35.54
(2 rows)

And the total sum of fair + nice ratings is as 开发者_StackOverflow社区expected four:

# select
count(nullif(r.fair, false)) +
count(nullif(r.nice, false)) -
count(nullif(r.fair, true)) -
count(nullif(r.nice, true))
 from pref_rep r where id='DE9013';
 ?column?
----------
        4
(1 row)

My question is: why do I get the user 9013 in the list below, where I'm trying to find all users who played more than 30 completed games and have the ratings (fair + nice) higher than 30?

# select substring(m.id from 3)::bigint, 3
from pref_match m, pref_rep r
where m.id=r.id and
m.id like 'DE%'
group by m.id
having (sum(m.completed) > 30 and
count(nullif(r.fair, false)) +
count(nullif(r.nice, false)) -
count(nullif(r.fair, true)) -
count(nullif(r.nice, true)) > 30) limit 3;
 substring | ?column?
-----------+----------
      9013 |        3
      8692 |        3
      7059 |        3
(3 rows)

Using PostgreSQL 8.4.7 with CentOS 5.7 / 64 bit

In your first query, you're only selecting from pref_rep. In the second query, you join pref_rep to pref_match, ostensibly in a many to many relationship. For a given user, every row in pref_match will join to every row pref_rep. For example, if user 9013 has 2 rows in pref_match and 10 rows in pref_rep, you will get 20 rows back! That is why the counts from pref_match are higher with the join than without the join.

I suggest you separately aggregate the two tables by user, then join the results. Something like this should work:

select substring(ma.id from 3)::bigint, 3
from (
   select r.id
   from pref_rep r
   where r.id like 'DE%' --yuck!
   group by r.id
   having (count(nullif(r.fair, false)) +
           count(nullif(r.nice, false)) -
           count(nullif(r.fair, true)) -
           count(nullif(r.nice, true)) > 30)
) ra
join (
   select m.id
   from pref_match m
   where m.id like 'DE%' --yuck!
   group by m.id
   having sum(m.completed) > 30
) ma
on ra.id = ma.id 
;