| 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: | Whole Thread | Raw Message | 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 | 
| 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 |