Re: group by weirdness

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