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(at)fuhr(dot)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