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'
;
begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
