Postgres Pro
Downloads
Home   >   mailing lists

Thread: invalid 'having' clause

invalid 'having' clause

From
"Iain"
Date:
Hi,

Just a quick question out of curiosity, I was just wondering if this is 
supposed to be valid sql:

select count(*) as cnt
from sometable
group by somecolumn
having cnt > 1

This isn't valid in pg (7.4.6), but this is:

select count(*)
from sometable
group by somecolumn
having count(*) > 1

Does anyone have any idea?

I can't remember what other databases do, and I'm not so familiar with the 
standards, but I just thought I'd mention it anyway.

regards
Iain

Re: invalid 'having' clause

From
Tom Lane
Date:
"Iain" <iain@mst.co.jp> writes:
> Just a quick question out of curiosity, I was just wondering if this is 
> supposed to be valid sql:

> select count(*) as cnt
> from sometable
> group by somecolumn
> having cnt > 1

No.  The HAVING clause logically executes before the SELECT output list
does, so it makes no sense for it to refer to the output list entries.
        regards, tom lane

Re: invalid 'having' clause

From
"Iain"
Date:
OK, thanks. That seems to make sense.

regards
Iain
----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Iain" <iain@mst.co.jp>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, December 02, 2004 11:54 AM
Subject: Re: [SQL] invalid 'having' clause 


> "Iain" <iain@mst.co.jp> writes:
>> Just a quick question out of curiosity, I was just wondering if this is 
>> supposed to be valid sql:
> 
>> select count(*) as cnt
>> from sometable
>> group by somecolumn
>> having cnt > 1
> 
> No.  The HAVING clause logically executes before the SELECT output list
> does, so it makes no sense for it to refer to the output list entries.
> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org