Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D5C969F95AE for ; Wed, 22 Aug 2007 09:13:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 72630-05-2 for ; Wed, 22 Aug 2007 09:12:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web50805.mail.re2.yahoo.com (web50805.mail.re2.yahoo.com [206.190.38.114]) by postgresql.org (Postfix) with SMTP id 04A1E9F95AB for ; Wed, 22 Aug 2007 09:13:02 -0300 (ADT) Received: (qmail 51469 invoked by uid 60001); 22 Aug 2007 12:13:01 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=yU2kH0SYVxPt7I8o03deuAd8f4ne5pgYDEmKf3Hm5f/FKP0qTnnLbg4AHIn9r0aUZLvm2ckgMvUwefU25C9pR6DNi+bQlFaVfpL9SFoCR7Yd076Sa0YHITNIoccSnD2b6i+IuMVrZv33OHwWJkstd6c9mVYlg/dktJdyXrYtLc4=; X-YMail-OSG: _poK85cVM1l8IOsG0Un3ew6CFZekr3udStiP4TKNJyEbnPFMhThsRXYyPkPEWACTqj2vQmstlJz0T9oLPrGfyaBeq8SEEmHNRUZ9ibbLaZtqT0nIn9wjxsL_s4REJMUdQTf2xAy2JqTURUhQQ43MxqAZ6w-- Received: from [211.27.120.121] by web50805.mail.re2.yahoo.com via HTTP; Wed, 22 Aug 2007 05:13:01 PDT Date: Wed, 22 Aug 2007 05:13:01 -0700 (PDT) From: roopa perumalraja Subject: Re: Solution to retrieve first and last row for each minute To: Christian Kindler , pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1134504344-1187784781=:51060" Content-Transfer-Encoding: 8bit Message-ID: <454693.51060.qm@web50805.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/193 X-Sequence-Number: 29131 --0-1134504344-1187784781=:51060 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi Christian, Thanks for your reply. Is it possible to use sub query to do this without using the IF ELSE LOOP? Cheers Roopa Christian Kindler wrote: Hi! Do something like this http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&view=markup and replace the max / min calculation with a count calculation. Cheers Chris On Wed, August 22, 2007 9:25 am, roopa perumalraja wrote: > Hi all, > > I have a table trans with the data > > price | volume | date | time > : > : > > I need to get the first and last price per every minute along with > count, average, maximum, minumum of the price and sum of the volume . > Right now I have my query which calculates count, maximum, minimum and > average. > > select trnew.date, trnew.trunc_time, count(*) as count, > avg(trnew.price) as avg_price, > sum(trnew.price*trnew.volume)/sum(trnew.volume) as vwap, > max(trnew.price) as high_price, min(trnew.price) as low_price, > sum(trnew.volume) as sum_volume from (select tr.date, > date_trunc('minute', tr.time) - interval '4 hour' as trunc_time, > tr.price, tr.volume from trans tr > where tr.time between '13:30:00.00' and '20:00:0.00' ) as trnew > group by trnew.date, trnew.trunc_time order by trnew.date, > trnew.trunc_time; > > How do I add first and last price for each minute to this query? > > Thanks a lot in advance. > > Cheers. > > > --------------------------------- > Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user > panel and lay it on us. -- cu Chris Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser --------------------------------- Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. --0-1134504344-1187784781=:51060 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit

Hi Christian,

Thanks for your reply. Is it possible to use sub query to do this without using the IF ELSE LOOP?

Cheers

Roopa

Christian Kindler <christian.kindler@gmx.net> wrote:

Hi!

Do something like this

http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&view=markup

and replace the max / min calculation with a count calculation.

Cheers
Chris

On Wed, August 22, 2007 9:25 am, roopa perumalraja wrote:
> Hi all,
>
> I have a table trans with the data
>
> price | volume | date | time

> :
> :
>
> I need to get the first and last price per every minute along with
> count, average, maximum, minumum of the price and sum of the volume .
> Right now I have my query which calculates count, maximum, minimum and
> average.
>
> select trnew.date, trnew.trunc_time, count(*) as count,
> avg(trnew.price) as avg_price,
> sum(trnew.price*trnew.volume)/sum(trnew.volume) as vwap,
> max(trnew.price) as high_price, min(trnew.price) as low_price,
> sum(trnew.volume) as sum_volume from (select tr.date,
> date_trunc('minute', tr.time) - interval '4 hour' as trunc_time,
> tr.price, tr.volume from trans tr
> where tr.time between '13:30:00.00' and '20:00:0.00' ) as trnew
> group by trnew.date, trnew.trunc_time order by trnew.date,
> trnew.trunc_time;
>
> How do I add first and last price for each minute to this query?
>
> Thanks a lot in advance.
>
> Cheers.
>
>
> ---------------------------------
> Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user
> panel and lay it on us.

--
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. --0-1134504344-1187784781=:51060--