Postgres Pro
Downloads
Home   >   mailing lists

Thread: having clause question

having clause question

From
Shane Wegner
Date:
Hello,

I am attempting to convert a query from MySQL to PostgreSQL
and am having some trouble using an aggregate column in a
having clause.  As a quick example, take the following SQL
script:

create table names(
firstname varchar(40) not null,
lastname varchar(40) not null
);

insert into names values('John','Smith');
insert into names values('Bob','Smith');
insert into names values('Mike','Skinner');

I want to retrieve any last names with more than 1
occurence in the table.  Under MySQL, this query does the
trick.
select lastname,count(*) as c from names group by lastname
having c > 1;

But under PG, it errors out
ERROR:  column "c" does not exist

Is it possible to do a query like this with PostgreSQL?

Thanks,
Shane

--
Shane Wegner
http://www.cm.nu/~shane/

Re: having clause question

From
Ian Barwick
Date:
On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner
<shane-keyword-pgsql.a1e0d9@cm.nu> wrote:
> Hello,
(...)
> I want to retrieve any last names with more than 1
> occurence in the table.  Under MySQL, this query does the
> trick.
> select lastname,count(*) as c from names group by lastname
> having c > 1;
>
> But under PG, it errors out
> ERROR:  column "c" does not exist
>
> Is it possible to do a query like this with PostgreSQL?

select lastname,count(*) as c from names group by lastname having count(*) > 1;

HTH

Ian Barwick
barwick@gmail.com

Re: having clause question

From
Tom Lane
Date:
Ian Barwick <barwick@gmail.com> writes:
> On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner
> <shane-keyword-pgsql.a1e0d9@cm.nu> wrote:
>> Under MySQL, this query does the trick.
>> select lastname,count(*) as c from names group by lastname
>> having c > 1;
>>
>> Is it possible to do a query like this with PostgreSQL?

> select lastname,count(*) as c from names group by lastname having count(*) > 1;

Just to expand on that: MySQL's abbrevation is not legal SQL, and it's
not even very sensible, because logically speaking the SELECT output
list is only evaluated after (and if) the HAVING clause succeeds.
So it makes no sense for the HAVING clause to refer to SELECT values.

Postgres does optimize the case of multiple identical aggregate-function
invocations in a query, BTW, so the apparent inefficiency is not real.

            regards, tom lane