Re: [GENERAL] Aggregate functions not allowed in WHERE clause

[Michael Fuhr]

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Aggregate functions not allowed in WHERE clause

[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

Cordially
-- 
   @..@  Ricardo Naranjo FacciniTel: (1) 257-9832
  () Ingeniero CivilCalle 95 #30-61 int 8
 ( __ )M.Sc. Ing. de Sistemas y Comp. Barrio La Castellana
 ^^ ~~ ^^[EMAIL PROTECTED]   Bogotá D.C.
   SKINAColombia, S.A.
IT Solutions http://www.skinait.com


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Aggregate functions not allowed in WHERE clause

[Michael Fuhr]

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

Re: [GENERAL] Aggregate functions not allowed in WHERE clause

[Christopher Browne]

Quoth [EMAIL PROTECTED] (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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Aggregate functions not allowed in WHERE clause

[pradeep singh]

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 [EMAIL PROTECTED] 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 

---(end of broadcast)---
TIP 6: explain analyze is your friend