From: Josh Berkus <josh@agliodbs.com>
To: Joseph Shraibman <jks@selectacast.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: group by weirdness
Date: Mon, 10 Sep 2001 21:06:48 -0700
Message-ID: <web-116932@davinci.ethosmedia.com> (raw)
In-Reply-To: <3B9D72B6.5050600@selectacast.net>
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, jks@selectacast.net
Subject: Re: group by weirdness
In-Reply-To: <web-116932@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox