Re: join and having clause

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Friday, December 30, 2011 6:16 PM
To: pgsql-general
Subject: [GENERAL] 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.