Search Postgresql Archives

join and having clause

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

I have an 8.4.9 table, where users can
assess other users (i.e. "nice" vs. "not nice"):

# \d pref_rep
                                       Table "public.pref_rep"
   Column   |            Type             |                         Modifiers
------------+-----------------------------+-----------------------------------------------------------
 id         | character varying(32)       |
 nice       | boolean                     |

and then another table with purchased VIP-status
as timestamp (can be NULL if never purchased):

# \d pref_users
                Table "public.pref_users"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       | not null
 vip        | timestamp without time zone |

As a Xmas present (russian server,
thus different Xmas date :-) I'd like to award
a week of "VIP-status" to all "nice" users.

I can fetch a list of "nice" users here:

# select r.id, count(nullif(r.nice, false)) - count(nullif(r.nice, true))
from pref_rep r group by r.id
having count(nullif(r.nice, false))-count(nullif(r.nice, true)) > 0;
-------------------------+----------
 DE10011                 |        2
 DE10016                 |       35
 DE10095                 |       79

But when I try to join it with pref_users table:

# select r.id, u.vip, count(nullif(r.nice, false)) - count(nullif(r.nice, true))
from pref_rep r, pref_users u group by r.id
having r.id=u.id and count(nullif(r.nice, false))-count(nullif(r.nice,
true)) > 0;
ERROR:  column "u.vip" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 1: select r.id, u.vip, count(nullif(r.nice, false)) - count(nul...

What to do? (besides stopping to harass users :-)

Regards
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux