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.
