Re: group by weirdness

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(at)selectacast(dot)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