Re: group by weirdness

Joseph,

The subject line could describe a lot of what I see outside my house
every day (I live in San Francisco CA).

> Could someome explain these error messages to me? Why am I being
> asked to group by j.id?

Because you've asked the db engine to count on mj.mid. The parser want
you to be specific about whether the other columns are being aggregated
or not.

> And why is the subquery worried about ml.oid if ml.oid is used in
> an aggregate?

> playpen=# select j.id, j.created, count(mj.mid),
> playpen-# (select count(ml.oid) where ml.state <> 11),
> playpen-# (select count(ml.oid) where ml.state IN(2,5) )
> playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND
> ml.jid = j.id
> playpen-# group by j.id, j.created;
> ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query

Because you're trying to aggregate two aggregates which are sub-selected
in the FROM clause ... a very painful way to not get the results you're
looking for. Even if you fixed the GROUPing problem, this query
wouldn't parse for other reasons. For example, the subselects you've
chosen would return the same count for every row, the total of ml.oid in
the database.

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;

-Josh Berkus

PS. Thanks for providing such complete data with your question!

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco