Postgres Pro
Downloads
Home   >   mailing lists

Thread: Aggregates in WHERE clause?

Aggregates in WHERE clause?

From
"Ruben Gouveia"
Date:
<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>

Re: Aggregates in WHERE clause?

From
Stephan Szabo
Date:
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.

Re: Aggregates in WHERE clause?

From
"Richard Broersma"
Date:
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

Re: Aggregates in WHERE clause?

From
Tom Lane
Date:
"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

Re: Aggregates in WHERE clause?

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


From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Ruben Gouveia
Sent: Wednesday, September 10, 2008 7:11 PM
To: pgsql-sql
Subject: [SQL] Aggregates in WHERE clause?

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

********** Error **********

ERROR: aggregates not allowed in WHERE clause
SQL state: 42803

Is there away around this? Should i create a function to populate a variable that can be used in it's place...will that even work?