Mark, Change your query to this:
SELECT id, count(*) FROM mytable GROUP BY id HAVING count(*) > 2; -----Original Message----- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Mark Fenbers Sent: Thursday, March 18, 2010 10:07 AM To: pgsql-sql@postgresql.org Subject: [SQL] Simple aggregate query brain fart 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
