Обсуждение: Aggregates in WHERE clause?

<div dir="ltr">I tried to do the following and got the following error message:<br /><br /><span style="color: rgb(51,
51,255);">select employee,count(distinct tasks)</span><br style="color: rgb(51, 51, 255);" /><span style="color:
rgb(51,51, 255);">        from job</span><br style="color: rgb(51, 51, 255);" /><span style="color: rgb(51, 51,
255);">       where greatest(max(last_job_date),max(last_position_date)) < 2008-08-28 + integer '1'</span><br
style="color:rgb(51, 51, 255);" /><span style="color: rgb(51, 51, 255);">        group by employee;</span><br /><br
/><fontcolor="#cc0000">ERROR:  aggregates not allowed in WHERE clause<br /><br />********** Error **********<br /><br
/>ERROR:aggregates not allowed in WHERE clause<br />SQL state: 42803</font><br /><br />Is there away around this?
Shouldi create a function to populate a variable that can be used in it's place...will that even work?<br /><br /><br
/></div>

On Wed, 10 Sep 2008, Ruben Gouveia wrote:

> I tried to do the following and got the following error message:
>
> 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

You probably want to look at some variant with HAVING, assuming you intend
those max()s to be based on the employee groups.

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

"Richard Broersma" <richard.broersma@gmail.com> writes:
> On Wed, Sep 10, 2008 at 4:11 PM, Ruben Gouveia <rubes7202@gmail.com> wrote:
>> ERROR:  aggregates not allowed in WHERE clause

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

The way I like to think about it is "WHERE filters rows before aggregate
functions are computed; HAVING filters them afterwards".  Seen in those
terms, it's obvious why WHERE can't contain any aggregates.
        regards, tom lane