Re: Aggregates in WHERE clause? - Mailing list pgsql-sql

On Wed, Sep 10, 2008 at 4:11 PM, Ruben Gouveia <rubes7202@gmail.com> wrote:

> select employee,count(distinct tasks)
>         from job
>         where greatest(max(last_job_date),max(last_position_date)) <
> 2008-08-28 + integer '1'
>         group by employee;
>
> ERROR:  aggregates not allowed in WHERE clause
>

   select employee,count(distinct tasks)     from job
group by employee  having greatest(max(last_job_date),max(last_position_date))          < 2008-08-28 + integer '1';

Having clause works on agregates.  If you want to force it in the
where, you need to put the groupby in a sub-query.

-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug