Simple aggregate query brain fart

From: Mark Fenbers <Mark(dot)Fenbers(at)noaa(dot)gov>
To: pgsql-sql(at)postgresql(dot)org
Subject: Simple aggregate query brain fart
Date: 2010-03-18 15:06:44
Message-ID: 4BA24184.7060603@noaa.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 Content-Type Size
Mark_Fenbers.vcf text/x-vcard 402 bytes

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Plugge, Joe R. 2010-03-18 15:10:38 Re: Simple aggregate query brain fart
Previous Message silly sad 2010-03-18 10:37:24 Re: MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date