Re: Simple aggregate query brain fart - Mailing list pgsql-sql

From Plugge, Joe R.
Subject Re: Simple aggregate query brain fart
Date
Msg-id BD69807DAE0CE44CA00A8338D0FDD08302CF162D5E@oma00cexmbx03.corp.westworlds.com
Whole thread Raw
In response to Simple aggregate query brain fart  (Mark Fenbers <Mark.Fenbers@noaa.gov>)
List 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@postgresql.org [mailto:pgsql-sql-owner@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



pgsql-sql by date:

Previous
From: Mark Fenbers
Date:
Subject: Simple aggregate query brain fart
Next
From: Tom Lane
Date:
Subject: Re: Simple aggregate query brain fart