On Mar 16, 2011, at 3:12 AM, Alexander Farber wrote: > Unfortunately I get: > > # select u.id, u.first_name, sum(m.completed) from pref_users u, > pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed) >> 30 group by u.id, u.first_name order by sum desc limit 3; > ERROR: aggregates not allowed in WHERE clause > LINE 1: ...f_match m where u.id=m.id and u.id like 'DE%' and sum(m.comp... >
My Bad... Missed you have to use having clause as given below: select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' group by u.id, u.first_name having sum(m.completed > 30 order by sum desc limit 3; > > On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar > <vibhor.ku...@enterprisedb.com> wrote: >> >> On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote: >> >>> # select u.id, u.first_name, sum(m.completed) >>> from pref_users u, pref_match m >>> where u.id=m.id and u.id like 'DE%' and >>> sum > 30 group by u.id, u.first_name >>> order by sum desc limit 3; >>> >>> ERROR: column "sum" does not exist >>> LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum > 30 g... >>> >>> Any suggestions please? I've tried "... sum(m.completed) as total" too.. >> >> >> Try following: >> select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m >> where u.id=m.id and u.id like 'DE%' and sum(m.completed > 30 group by u.id, >> u.first_name order by sum desc limit 3; >> > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
