pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joseph Shraibman <jks@selectacast.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: group by weirdness
Date: Mon, 10 Sep 2001 22:11:02 -0400
Message-ID: <3B9D72B6.5050600@selectacast.net> (raw)

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@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com




view thread (7+ messages)  latest in thread

Message-ID: <3B9D72B6.5050600@selectacast.net>
Permalink:  ../3B9D72B6.5050600@selectacast.net/
Also on:    postgresql.org/message-id/3B9D72B6.5050600@selectacast.net

 · 

reply

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jks@selectacast.net
  Subject: Re: group by weirdness
  In-Reply-To: <3B9D72B6.5050600@selectacast.net>

* 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