Aggregate functions in ANSI SQL
- by morpheous
I want to use multiple aggregate functions in a query. All the examples i have seem on aggregate functions however, are trivial.
Typically, they are of the form:
SELECT field1,agg_func1, agg_func2 GROUP BY SOME_COLUMNS HAVING agg_func1 OP SOME_SCALAR
Where:
OP: is a boolean operator (e.g. <, = etc)
SOME_SCALAR: is a scalar (i.e. a constant number)
What I want to know is if it is possible to write (IN ANSI SQL) queries like:
SELECT field1,agg_func1, agg_func2, agg_func3 GROUP BY SOME_COLUMNS HAVING (agg_func1 OP1 agg_func2) OP2 (agg_func2 OP3 agg_func3)
Where:
OP[N] are boolean operators or ANSI SQL clause operators like 'BETWEEN', 'LIKE', 'IN' etc.
Also, assuming this is possible (I have not seen any documentation saying otherwise) are there any efficiency/performance considerations (i.e. penalties) when the HAVING clause consists of a boolean expression combining the output of the aggregate functions - instead of the normal comparison of the output of the aggregate with a constant number (e.g. min('salary') 100 ) - which is often used in the most banal examples involving aggregate functions?
