From: Carl van Tast <vanTast@Pivot.at>
To: pgsql-sql@postgresql.org
Subject: Re: group by weirdness
Date: Sat, 15 Sep 2001 00:26:01 +0200
Message-ID: <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com> (raw)
References: <3B9D72B6.5050600@selectacast.net>
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: vanTast@Pivot.at
Subject: Re: group by weirdness
In-Reply-To: <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.com>
References: <3B9D72B6.5050600@selectacast.net> <dv05qt02nk45ivsa80nhhhekk38efq0gqg@4ax.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