Re: A join of 2 tables with sum(column) > 30

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...


On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar
<vibhor.kumar@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; 
>