Re: Aggregate functions not allowed in WHERE clause

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