-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber Sent: Friday, December 30, 2011 6:16 PM To: pgsql-general Subject: join and having clause 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 ---------------------------------------------------------------------------- -------- Simple, just add "u.vip" to your "GROUP BY" clause in the second query (right after r.id). Also, the "r.id = u.id" properly belongs in a "WHERE" clause and not the "HAVING" clause. My personal preference, however, is to use explicit JOIN syntax instead (in which case the JOIN gets the r.id = u.id and you can continue to omit the WHERE and using just the COUNT expressions in the HAVING; in fact, the absence of an aggregate function over any field in the HAVING clause is red-flag). That said, consider the following: For the first query this is probably easier to follow: SELECT r.id, SUM(CASE WHEN r.nice THEN 1 ELSE -1 END) AS nice_balance FROM pref_ref r GROUP BY r.id; Now to combine the two: SELECT id, u.vip, sub.nice_balance FROM ( <the query above> ) AS sub JOIN pref_users u USING (id) WHERE nice_balance > [some threshold value]; Include the HAVING clause in the sub-query to improve performance. If you ever start getting "...must appear in the GROUP BY clause..." messages often the best approach is to use sub-queries to break up the GROUP BY parts from the "extra-details" parts and then JOIN them together. If performance is acceptable you stop there otherwise you can refactor the query so that you do all the JOINing first and then only perform the GROUP BY at the end. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
