A query that doesn't work on 7.1

From: Kyle <kyle(at)actarg(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: A query that doesn't work on 7.1
Date: 2001-03-07 23:13:50
Message-ID: 3AA6C0AD.BC8A015A@actarg.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Here's a query that doesn't work on 7.1. Is this a bug or am I doing
something wrong?

The last two selects yield:

ERROR: Sub-SELECT uses un-GROUPed attribute m1.ropnum from outer query

Basically, everything works until I apply the avg() function and try to
aggregate the results.

drop table mtr;
create table mtr (
ttype varchar(2), --record type
ropnum int4, --order number
minum int4, --item number
pnum varchar(18),
tdate date,

primary key (ttype,ropnum,minum)
);

insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('po',1000,1,2000,'2001-Jan-30');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1000,1,2001,'2001-Jan-10');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1000,2,2002,'2001-Jan-12');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1000,3,2003,'2001-Jan-14');

insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('po',1001,1,2000,'2001-Feb-28');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1001,1,2011,'2001-Feb-01');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1001,2,2012,'2001-Feb-02');
insert into mtr (ttype,ropnum,minum,pnum,tdate) values
('wm',1001,3,2013,'2001-Feb-03');

--The finish date is represented by the tdate of a po type record
--The start date is found by the earliest of the wm type records with
the same ropnum,minum fields
--This lists the start and finish dates
select
(select min(tdate) from mtr where ttype = 'wm' and ropnum =
m1.ropnum) as start,
m1.tdate as finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;

--Now I try to find the average number of days between start and finish
for the part
select
avg(date_part('day',(start::datetime - finish::datetime)::timespan))
from
(select
(select min(tdate) from mtr where ttype = 'wm' and ropnum =
m1.ropnum) as start,
m1.tdate::datetime as finish
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
) as dates
;

--Here I try a different method
-- select
select
avg(date_part('day',((select min(tdate) from mtr where ttype = 'wm'
and ropnum = m1.ropnum)::datetime - m1.tdate::datetime)::timespan))
from mtr m1 where
m1.ttype = 'po' and
m1.pnum = '2000'
;

Attachment Content-Type Size
kyle.vcf text/x-vcard 185 bytes

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jia L Han 2001-03-07 23:15:43 ecpg on 7.0
Previous Message dev 2001-03-07 21:49:14 Re: work on rows