Обсуждение: Aggregate functions not allowed in WHERE clause

I have two tables, Claims and Logs, and I need to fish in for the id of
any
claim who have into the logs anything into the fields invoices or
payments

I think the best way to do this is by mean of:

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"

Anyone could help me to figure out this task please

Cordially
--
   @..@      Ricardo Naranjo Faccini            Tel: (1) 257-9832
  (----)     Ingeniero Civil                    Calle 95 #30-61 int 8
 ( >__< )    M.Sc. Ing. de Sistemas y Comp.     Barrio La Castellana
 ^^ ~~ ^^    gerencia@skinait.com               Bogotá D.C.
   SKINA                                        Colombia, S.A.
IT Solutions http://www.skinait.com

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

Quoth gramo.gnu@gmail.com (Ricardo Naranjo Faccini):
> I have two tables, Claims and Logs, and I need to fish in for the id of
> any
> claim who have into the logs anything into the fields invoices or
> payments
>
> I think the best way to do this is by mean of:
>
> 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"
>
> Anyone could help me to figure out this task please

You might consider using a HAVING clause to add those constraints at
the grouping level...

select claim_id
from logs
group by claim_id
having sum(logs.invoices) > 0 or sum(logs.payments) > 0;

You might need to have those sums in the outer select...
--
(reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc"))
http://linuxfinances.info/info/finances.html
"Microsoft has world class quality control" -- Arthur Norman

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

On Mon, Jun 12, 2006 at 08:40:29PM -0700, pradeep singh wrote:
> 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.

PostgreSQL has no such requirement; see "The GROUP BY and HAVING
Clauses" in the documentation:

http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-GROUP

"Tip:  Grouping without aggregate expressions effectively calculates
the set of distinct values in a column."

"Note that the aggregate expressions do not necessarily need to be
the same in all parts of the query."

Offhand I don't know if the SQL standard requires expressions in
the HAVING clause to be present in the select list -- can you cite
reference from the standard that supports the assertion that they
should be?

--
Michael Fuhr