Received: from tor-smtp1.netcom.ca (tor-smtp1.netcom.ca [207.181.101.69]) by hub.org (8.10.1/8.10.1) with ESMTP id e72FtHG82260 for ; Wed, 2 Aug 2000 11:55:17 -0400 (EDT) Received: from widgette (trt-on55-73.netcom.ca [216.123.100.73]) by tor-smtp1.netcom.ca (8.8.7-s-4/8.8.7) with SMTP id LAA23839; Wed, 2 Aug 2000 11:54:07 -0400 (EDT) Message-ID: <001a01bffc9a$d33544f0$49647bd8@WORKGROUP> From: "David Lloyd-Jones" To: "Thomas Lockhart" , "Itai Zukerman" Cc: References: <87ittju6e7.fsf@matt.w80.math-hat.com> <39883DD1.610A0954@alumni.caltech.edu> Subject: Re: Time Aggregates Date: Wed, 2 Aug 2000 11:37:44 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200008/12 "Thomas Lockhart" replied to: "Itai Zukerman" > > I'm currently doing this: > > SELECT symbol, date_trunc('minute', posted), > > min(price), max(price), avg(price) > > FROM trade > > GROUP BY symbol, date_trunc('minute', posted); > > to get a list of minute-averages of trade prices. I get the feeling > > that this is bad form, that I should be doing this some other way. Is > > that the case? > > Looks OK to me. If you are doing this *a lot* (i.e. many more queries > than inserts), then you might want to set up another column which > contains date_trunc('minute',posted) to avoid the calculation. Something > like > > create table trade ( > symbol text, > posted timestamp, > price integer, > mpost timestamp > ) > > then define a rule to update mpost when posted gets set (haven't done > that part). Tom, I want to have two inputs to a table -- 5 widgets at 6 dollars, nextline,7 widgets at 45 dollars, nextline 1 widget at 4...sort of thing -- and output to screen the $30, $315, $4 with those last three numbers also going back into the database. Is there any way of doing this withing the SQL, or does it have to be written in a scipt external to the database itself? Cheers, -dlj.