Re: Aggregate functions not allowed in WHERE clause

i think this query can be rewritten as

 SELECT claim_id,sum(invoices),sum(payments)
 FROM logs
 GROUP BY claim_id
 HAVING sum(invoices) > 0 OR sum(payments) > 0;


having clause can be used with aggregate functions but
those functions should be the part of column
list/expression list in the SELECT statement.

pradeep

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Mon, Jun 12, 2006 at 09:00:33PM -0500, Ricardo
> Naranjo Faccini wrote:
> > SELECT claim_id
> > FROM logs
> > WHERE (
> >     sum(logs.invoices) > 0
> >     OR
> >     sum(logs.payments) > 0
> > )
> > GROUP BY claim_id
> >
> > But Postgres claims "Aggregate functions not
> allowed in WHERE clause"
>
> I think you're looking for HAVING.  Does the
> following do what you
> want?
>
> SELECT claim_id
> FROM logs
> GROUP BY claim_id
> HAVING sum(invoices) > 0 OR sum(payments) > 0;
>
> --
> Michael Fuhr
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com