Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id ED28F9F98D4 for ; Wed, 22 Aug 2007 04:25:53 -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 12296-05 for ; Wed, 22 Aug 2007 04:25:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web50810.mail.re2.yahoo.com (web50810.mail.re2.yahoo.com [206.190.38.253]) by postgresql.org (Postfix) with SMTP id C100D9F98CA for ; Wed, 22 Aug 2007 04:25:48 -0300 (ADT) Received: (qmail 83737 invoked by uid 60001); 22 Aug 2007 07:25:47 -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=Epuh9PLYHgppXyptxyCvfQNGq9k32cmwy+4tLz4B79HlRr1RBs5k/oQflthIs2+RVnzofwIgRFG0JA9lfF9z2PSsvdhuCw8sFhk4/oGa+HVYi6tTCz50URH9FnJ3tELFSejXjQu9FUFe3tnm9kaDCL0A+CApjP+OoJrZIPsYBPc=; X-YMail-OSG: Rsgpj10VM1k6HmaYLW28JbQ4L_saAckVRMRvfPc3s_f0VQ5qGUrcW_jq.7XAT.nVXSgP3V_SEV4C8mopvbsRwjiZeKPPy_mmQ.xcs1P_l4q4QkZmtUKNHReQ8Ofh.uORHtLwF4g6g_xdmV58ONS1X_JtSw-- Received: from [203.134.174.70] by web50810.mail.re2.yahoo.com via HTTP; Wed, 22 Aug 2007 00:25:47 PDT Date: Wed, 22 Aug 2007 00:25:47 -0700 (PDT) From: roopa perumalraja Subject: Solution to retrieve first and last row for each minute To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-418476938-1187767547=:83589" Content-Transfer-Encoding: 8bit Message-ID: <729161.83589.qm@web50810.mail.re2.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.376 tagged_above=0 required=5 tests=AWL=1.374, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: * X-Archive-Number: 200708/189 X-Sequence-Number: 29127 --0-418476938-1187767547=:83589 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi all, I have a table trans with the data price | volume | date | time -------+--------+------------+-------------- 79.87 | 500 | 2006-06-01 | 13:30:14.262 79.87 | 900 | 2006-06-01 | 13:30:15.375 79.85 | 200 | 2006-06-01 | 13:30:17.381 79.85 | 500 | 2006-06-01 | 13:30:20.276 79.85 | 1900 | 2006-06-01 | 13:30:21.438 79.83 | 200 | 2006-06-01 | 13:30:23.388 79.83 | 600 | 2006-06-01 | 13:30:25.863 79.82 | 400 | 2006-06-01 | 13:30:27.838 79.82 | 400 | 2006-06-01 | 13:30:29.314 79.82 | 400 | 2006-06-01 | 13:30:30.776 79.81 | 400 | 2006-06-01 | 13:30:31.867 79.82 | 100 | 2006-06-01 | 13:30:32.446 79.82 | 100 | 2006-06-01 | 13:30:41.44 79.82 | 100 | 2006-06-01 | 13:30:42.712 79.82 | 400 | 2006-06-01 | 13:30:45.907 79.83 | 600 | 2006-06-01 | 13:30:48.245 79.83 | 400 | 2006-06-01 | 13:30:49.33 79.85 | 100 | 2006-06-01 | 13:30:53.282 79.89 | 700 | 2006-06-01 | 13:31:06.437 79.89 | 1100 | 2006-06-01 | 13:31:08.513 79.89 | 100 | 2006-06-01 | 13:31:12.318 79.89 | 2200 | 2006-06-01 | 13:31:16.867 79.89 | 2400 | 2006-06-01 | 13:31:19.832 79.89 | 1200 | 2006-06-01 | 13:31:22.361 79.89 | 1000 | 2006-06-01 | 13:31:34.93 79.88 | 600 | 2006-06-01 | 13:31:44.98 79.89 | 3200 | 2006-06-01 | 13:31:46.497 79.88 | 1100 | 2006-06-01 | 13:31:49.345 79.88 | 500 | 2006-06-01 | 13:31:52.362 79.88 | 300 | 2006-06-01 | 13:31:53.286 79.85 | 800 | 2006-06-01 | 13:31:54.309 79.84 | 1900 | 2006-06-01 | 13:31:55.834 79.84 | 100 | 2006-06-01 | 13:32:02.318 79.85 | 700 | 2006-06-01 | 13:32:05.975 79.84 | 600 | 2006-06-01 | 13:32:06.375 79.84 | 500 | 2006-06-01 | 13:32:07.904 79.85 | 500 | 2006-06-01 | 13:32:08.918 79.87 | 400 | 2006-06-01 | 13:32:18.782 79.88 | 200 | 2006-06-01 | 13:32:20.336 79.88 | 1600 | 2006-06-01 | 13:32:30.381 79.88 | 200 | 2006-06-01 | 13:32:34.912 79.88 | 700 | 2006-06-01 | 13:32:36.279 79.88 | 100 | 2006-06-01 | 13:32:36.806 79.88 | 1500 | 2006-06-01 | 13:32:38.795 79.9 | 400 | 2006-06-01 | 13:32:40.992 79.9 | 200 | 2006-06-01 | 13:32:49.892 79.9 | 400 | 2006-06-01 | 13:32:51.391 79.9 | 200 | 2006-06-01 | 13:33:00.274 79.91 | 100 | 2006-06-01 | 13:33:03.862 79.92 | 200 | 2006-06-01 | 13:33:11.787 79.91 | 500 | 2006-06-01 | 13:33:12.781 79.91 | 1000 | 2006-06-01 | 13:33:12.781 79.95 | 1400 | 2006-06-01 | 13:33:14.962 79.94 | 1000 | 2006-06-01 | 13:33:17.429 79.95 | 200 | 2006-06-01 | 13:33:19.865 79.93 | 200 | 2006-06-01 | 13:33:20.91 79.93 | 200 | 2006-06-01 | 13:33:21.281 79.93 | 2200 | 2006-06-01 | 13:33:24.363 79.93 | 600 | 2006-06-01 | 13:33:25.739 79.94 | 200 | 2006-06-01 | 13:33:27.436 79.93 | 1300 | 2006-06-01 | 13:33:29.375 79.93 | 600 | 2006-06-01 | 13:33:30.375 79.93 | 300 | 2006-06-01 | 13:33:32.352 79.92 | 1400 | 2006-06-01 | 13:33:33.279 79.93 | 200 | 2006-06-01 | 13:33:34.825 : : : 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. --0-418476938-1187767547=:83589 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi all,
 
I have a table trans with the data
 
 price | volume | date  |  time  
-------+--------+------------+--------------
 79.87 |    500 | 2006-06-01 | 13:30:14.262
 79.87 |    900 | 2006-06-01 | 13:30:15.375
  79.85 |    200 | 2006-06-01 | 13:30:17.381
 79.85 |    500 | 2006-06-01 | 13:30:20.276
 79.85 |   1900 | 2006-06-01 | 13:30:21.438
 79.83 |    200 | 2006-06-01 | 13:30:23.388
 79.83 |    600 | 2006-06-01 | 13:30:25.863
 79.82 |    400 | 2006-06-01 | 13:30:27.838
 79.82 |    400 | 2006-06-01 | 13:30:29.314
 79.82 |    400 | 2006-06-01 | 13:30:30.776
 79.81 |    400 | 2006-06-01 | 13:30:31.867
 79.82 |    100 | 2006-06-01 | 13:30:32.446
  79.82 |    100 | 2006-06-01 | 13:30:41.44
 79.82 |    100 | 2006-06-01 | 13:30:42.712
 79.82 |    400 | 2006-06-01 | 13:30:45.907
 79.83 |    600 | 2006-06-01 | 13:30:48.245
 79.83 |    400 | 2006-06-01 | 13:30:49.33
 79.85 |    100 | 2006-06-01 | 13:30:53.282
 79.89 |    700 | 2006-06-01 | 13:31:06.437
 79.89 |   1100 | 2006-06-01 | 13:31:08.513
 79.89 |    100 | 2006-06-01 | 13:31:12.318
 79.89 |   2200 | 2006-06-01 | 13:31:16.867
 79.89 |   2400 | 2006-06-01 | 13:31:19.832
 79.89 |   1200 | 2006-06-01 | 13:31:22.361
 79.89 |   1000 | 2006-06-01 | 13:31:34.93
 79.88 |    600 | 2006-06-01 | 13:31:44.98
 79.89 |   3200 | 2006-06-01 | 13:31:46.497
 79.88 |   1100 | 2006-06-01 | 13:31:49.345
 79.88 |    500 | 2006-06-01 | 13:31:52.362
 79.88 |    300 | 2006-06-01 | 13:31:53.286
 79.85 |    800 | 2006-06-01 | 13:31:54.309
 79.84 |   1900 | 2006-06-01 | 13:31:55.834
 79.84 |    100 | 2006-06-01 | 13:32:02.318
 79.85 |    700 | 2006-06-01 | 13:32:05.975
 79.84 |    600 | 2006-06-01 | 13:32:06.375
 79.84 |    500 | 2006-06-01 | 13:32:07.904
 79.85 |    500 | 2006-06-01 | 13:32:08.918
 79.87 |    400 | 2006-06-01 | 13:32:18.782
 79.88 |    200 | 2006-06-01 | 13:32:20.336
 79.88 |   1600 | 2006-06-01 | 13:32:30.381
 79.88 |    200 | 2006-06-01 | 13:32:34.912
 79.88 |    700 | 2006-06-01 | 13:32:36.279
 79.88 |    100 | 2006-06-01 | 13:32:36.806
 79.88 |   1500 | 2006-06-01 | 13:32:38.795
  79.9 |    400 | 2006-06-01 | 13:32:40.992
  79.9 |    200 | 2006-06-01 | 13:32:49.892
  79.9 |    400 | 2006-06-01 | 13:32:51.391
  79.9 |    200 | 2006-06-01 | 13:33:00.274
 79.91 |    100 | 2006-06-01 | 13:33:03.862
 79.92 |    200 | 2006-06-01 | 13:33:11.787
 79.91 |    500 | 2006-06-01 | 13:33:12.781
 79.91 |   1000 | 2006-06-01 | 13:33:12.781
 79.95 |   1400 | 2006-06-01 | 13:33:14.962
 79.94 |   1000 | 2006-06-01 | 13:33:17.429
 79.95 |    200 | 2006-06-01 | 13:33:19.865
 79.93 |    200 | 2006-06-01 | 13:33:20.91
 79.93 |    200 | 2006-06-01 | 13:33:21.281
 79.93 |   2200 | 2006-06-01 | 13:33:24.363
 79.93 |    600 | 2006-06-01 | 13:33:25.739
 79.94 |    200 | 2006-06-01 | 13:33:27.436
 79.93 |   1300 | 2006-06-01 | 13:33:29.375
 79.93 |    600 | 2006-06-01 | 13:33:30.375
 79.93 |    300 | 2006-06-01 | 13:33:32.352
 79.92 |   1400 | 2006-06-01 | 13:33:33.279
 79.93 |    200 | 2006-06-01 | 13:33:34.825
 :
 :
 :
 
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. --0-418476938-1187767547=:83589--