[SQL] Simple aggregate query brain fart

[Mark Fenbers]

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;
But this doesn't work because Pg won't allow aggregate functions in a 
where clause.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an 
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) 
instead of a column name, but how can I refer to my unnamed second 
column in my where clause?

Mark

<<attachment: Mark_Fenbers.vcf>>-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql