Re: Solution to retrieve first and last row for each minute
This is a discussion on Re: Solution to retrieve first and last row for each minute within the pgsql Sql forums, part of the PostgreSQL category; --> Hi! Do something like this http://fimi.cvs.sourceforge.net/fimi....3&view=markup and replace the max / min calculation with a count calculation. Cheers Chris ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-19-2008, 05:49 PM
| ||||
| ||||
 Re: Solution to retrieve first and last row for each minute Hi! Do something like this http://fimi.cvs.sourceforge.net/fimi....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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate      |
Linear Mode
