Thread: 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