Skip site navigation (1) Skip section navigation (2)

Problem using Having in a sub-query wit the Count function.

From: Matthew <matt(at)ctlno(dot)com>
To: "'pgsql-general(at)hub(dot)org'" <pgsql-general(at)hub(dot)org>, "'pgsql-interfaces(at)hub(dot)org'" <pgsql-interfaces(at)hub(dot)org>
Subject: Problem using Having in a sub-query wit the Count function.
Date: 1999-03-23 18:46:10
Message-ID: A043233669F9D111B99700A0C92376CA0DD6F1@srv.ctlno.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-interfaces
> We are trying to execute a query that has several sub-queries embedded
> in it.  Below is a snippet of the sql code.
> 
> "Select ordnum from ordinace where dept='Finance' and ordnum in
> (Select ordnum from squareview where square='A') and ordnum in (select
> ordnum from keywordview where keyword='Parade' or keyword='Public
> Gathering' group by ordnum having count(ordnum) =2)"
> 
> The two tables in the sub-queries (squareview and keywordview) or
> views created between two tables.
> There are roughly about 20000 records in the keywordview view.
> 
> When we execute the query, failing at the keywordview subquery, saying
> the aggregate function in the having clause must appear on the right
> side. (?)  When we take the having clause out, and strictly have the
> group by, it takes 30secs to 3mins. to return with the valid
> recordset.
> 
> The funny thing is, as a stand alone query on it's own, the
> keywordview query works fine.  It's very quick and has no problem with
> the having clause.
> 
> I was wondering if anyone else has either had this problme using
> aggregate functions with the having clause in a subquery, or could
> anyone give me any information of successfully executing something
> similar to this.
> 
> Any infomation would be appreciated.
> 
> Thanks in advance,
> 
> Steve
> steve(at)ctlno(dot)com

Responses

pgsql-interfaces by date

Next:From: Clark EvansDate: 1999-03-23 23:08:09
Subject: Re: [INTERFACES] Other JDBC Performance Question
Previous:From: Steve ClaverieDate: 1999-03-23 18:44:24
Subject: Problem using Having in a sub-query wit the Count function.

pgsql-general by date

Next:From: Bruce GuenterDate: 1999-03-23 20:04:42
Subject:
Previous:From: Steve ClaverieDate: 1999-03-23 18:44:24
Subject: Problem using Having in a sub-query wit the Count function.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group