Aggregate functions not allowed in WHERE clause

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

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

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