Assuming I have the following aggregate functions:
- AGG1
- AGG2
- AGG3
- AGG4
Is it possible to write valid SQL (in a db agnostic way) like this:
SELECT [COL1, COL2 ....], AGG1(param1), AGG2(param2) FROM [SOME TABLES]
WHERE [SOME CRITERIA]
HAVING AGG3(param2) >-1 and AGG4(param4) < 123
GROUP BY COL1, COL2, ... COLN
ORDER BY COL1, COLN ASC
LIMIT 10
Where COL1 ... COLN are columns in the tables being queried, and param1 ... paramX are parameters passed to the AGG funcs.
Note: AGG1 and AGG2 are returned in the results as columns (but do not appear in the HAVING CLAUSE, and AGG3 and AGG4 appear in the HAVING CLAUSE but are not returned in the result set.
Ideally, I want a DB agnostic answer to the solution, but if I have to be tied to a db, I am using PostgreSQL (v9.x).
Edit
Just a matter of clarification: I am not opposed to using GROUP BY in the query. My SQL is not very good, so the example SQL above may have been slightly misleading. I have edited the pseudo sql statement above to hopefully make my intent more clear.
The main thing I wanted to find out was whether a select query that used AGG functions could:
- Have agg functions values in the returned column without them being specified in a HAVING clause.
- Have agg functions specified in a HAVING clause, but are not returned in the result set.
From the answers I have received so far, it would seem the answer to both questions is YES. The only think I have to do to correct my SQL is to add a GROUP BY clause to make sure that the returned rows are unique.
PostgreSQL major version include the first digit after the dot, thus "PostgreSQL (v9.x)" is not specific enough. As @kekekela said, there is no (cheap) completely db agnostic way. Even between PostgreSQL 9.0 and 9.1 there is an important syntactical difference.
If you had only the grouped values
AGG1(param1), AGG2(param2)
you would get away without providing an explicitGROUP BY
clause. Since you mix grouped and non-grouped columns you have to provide aGROUP BY
clause with all non-grouped columns that appear in theSELECT
. That's true for any version of PostgreSQL. Read about GROUP BY and HAVING it in the manual.Starting with version 9.1, however, once you list a primary key in the
GROUP BY
you can skip additional columns for this table and still use them in theSELECT
list. The release notes for version 9.1 tell us:Concerning parameters
Do you intend to feed a constant value to an aggregate function? What's the point? The docs tell us
Or do you want those parameters to be column names? That kind of dynamic SQL works as long as the statement is generated before committing to the database. Does not work for prepared statements or simple
sql
orplpgsql
functions. You have to use EXECUTE in aplpgsql
function for that purpose.As safeguard against SQLi use the
USING $1, $2
syntax for values and quote_ident() for your column or table names.