Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8EMnj746335 for ; Fri, 14 Sep 2001 18:49:45 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8EMQNR57494 for pgsql-sql@postgresql.org; Fri, 14 Sep 2001 18:26:23 -0400 (EDT) (envelope-from news) From: Carl van Tast X-Newsgroups: comp.databases.postgresql.sql Subject: Re: group by weirdness Date: Sat, 15 Sep 2001 00:26:01 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 131 Message-ID: References: <3B9D72B6.5050600@selectacast.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Newsreader: Forte Agent 1.8/32.548 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/108 X-Sequence-Number: 4773 Joseph, you might want to try: CREATE VIEW mj1 (jid, cnt) AS SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid; CREATE VIEW ml1 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid; CREATE VIEW ml2 (jid, cnt) AS SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2,5) GROUP BY jid; SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN mj1 ON (j.id = mj1.jid) LEFT JOIN ml1 ON (j.id = ml1.jid) LEFT JOIN ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; I did not test this with PostgreSQL, but you get the idea. Probably PG is even smart enough to handle it all in one: SELECT j.id, j.created, mj1.cnt, ml1.cnt, ml2.cnt FROM j LEFT JOIN (SELECT jid, COUNT(mid) cnt FROM mj GROUP BY jid) mj1 ON (j.id = mj1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state <> 11 GROUP BY jid) ml1 ON (j.id = ml1.jid) LEFT JOIN (SELECT jid, COUNT(*) cnt FROM ml WHERE state IN (2, 5) GROUP BY jid) ml2 ON (j.id = ml2.jid) WHERE j.fkey = 1; HTH, Carl van Tast On Tue, 11 Sep 2001 02:26:32 +0000 (UTC), jks@selectacast.net (Joseph Shraibman) wrote: >Could someome explain these error messages to me? Why am I being asked to group by j.id? > And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? > >Follows: script, then output. > > >select version(); >create table j (id int, created timestamp default current_timestamp, fkey int); >create table mj (jid int, mid int); >create table ml (jid int, created timestamp default current_timestamp, state int); > >insert into j (id, fkey) values (1, 1); >insert into j (id, fkey) values (2, 1); > >insert into mj values(1, 1); >insert into mj values(1, 2); >insert into mj values(2, 3); >insert into mj values(2, 4); >insert into mj values(2, 5); > >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 2); >insert into ml(jid, state) values (1, 11); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 2); >insert into ml(jid, state) values (2, 11); > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; > >select j.id, j.created, count(mj.mid), > (select count(ml.oid) where ml.state <> 11), > (select count(ml.oid) where ml.state IN(2,5) ) >FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id >group by j.id, j.created; > >drop table j; >drop table mj ; >drop table ml; > >=================================================================================================== > >playpen=# select version(); > version >--------------------------------------------------------------------- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 >(1 row) > >playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); >CREATE >playpen=# create table mj (jid int, mid int); >CREATE >playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); >CREATE >playpen=# >playpen=# insert into j (id, fkey) values (1, 1); > >playpen=# insert into ml(jid, state) values (2, 11); >INSERT 329676 1 >playpen=# >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 ; >ERROR: Attribute j.id must be GROUPed or used in an aggregate function >playpen=# >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 >playpen=# >playpen=# drop table j; >DROP >playpen=# drop table mj ; >DROP >playpen=# drop table ml; >DROP