From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | group by weirdness |
Date: | 2001-09-11 02:11:02 |
Message-ID: | 3B9D72B6.5050600@selectacast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-09-11 04:06:48 | Re: group by weirdness |
Previous Message | Jack | 2001-09-11 01:31:55 | array variables in pl/pgsql |