Received: from p1.selectacast.net (p1.selectacast.net [64.15.230.162]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8DLrj747696 for ; Thu, 13 Sep 2001 17:53:45 -0400 (EDT) (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [64.7.9.58]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id RAA18832; Thu, 13 Sep 2001 17:53:42 -0400 Message-ID: <3BA12AE6.9050708@selectacast.net> Date: Thu, 13 Sep 2001 17:53:42 -0400 From: Joseph Shraibman Organization: Xtenit, Inc. http://www.xtenit.com User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.3) Gecko/20010801 X-Accept-Language: en-us MIME-Version: 1.0 To: Josh Berkus CC: pgsql-sql Subject: Re: group by weirdness References: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200109/68 X-Sequence-Number: 4714 Josh Berkus wrote: > Try putting your sub-selects in the FROM clause instead. (Personally, > I've never found a use for sub-selects in the SELECT clause) > > SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount > FROM j, mj, > (SELECTjid, COUNT(oid) as mcount FROM ml > WHERE ml.state <> 11 GROUP BY jid) ma1, > (SELECT jid, COUNT(oid) as mcount FROM ml > WHERE ml.state in (2,5) GROUP BY jid) ma2 > WHERE j.fkey = 1 AND mj.jid = j.id > AND ma1.jid = j.id AND ma2.jid = j.id > GROUP BY j.id, j.created, ma1.mcount, ma2.mcount; > OK that worked for this simple example, but on my real database the performance was horrible, and it didn't work for then there were zero entries in ml (this bites me sometimes, when the AND clause keeps things from working as I think they should). Putting the selects in the SELECT solved both problems. I took out the 'AND ml.jid = j.id' from the outer WHERE (would have also excluded cases where there were zero entries in ml) and only refrenced ml in the subselect. Thanks for your help. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com