Re: group by weirdness - Mailing list pgsql-sql

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);
><snip>
>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