From: | "Plugge, Joe R(dot)" <JRPlugge(at)west(dot)com> |
---|---|
To: | Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Simple aggregate query brain fart |
Date: | 2010-03-18 15:10:38 |
Message-ID: | BD69807DAE0CE44CA00A8338D0FDD08302CF162D5E@oma00cexmbx03.corp.westworlds.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Mark,
Change your query to this:
SELECT id, count(*) FROM mytable GROUP BY id HAVING count(*) > 2;
-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To: pgsql-sql(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-03-18 15:11:13 | Re: Simple aggregate query brain fart |
Previous Message | Mark Fenbers | 2010-03-18 15:06:44 | Simple aggregate query brain fart |