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(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com