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 |
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 |