SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Hybrid View
-
May 3, 2008, 05:09 #1
- Join Date
- May 2008
- Posts
- 15
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SELECT min, max, open, close (trades)
I have a table (trades) with some stock-trades
It has the following fields
id (autogenerate, key)
stock (int)
tradetime (datetime)
price (decimal (10,4))
stockcount (int)
Now it would like to make an SELECT from 'trades' that will give me
min (the smallest price value)
max (the largest price value)
open (the first price value, - i.e. the price for the trade with smallest tradetime and should there be more smallest id)
close (the last, - like open but opposite)
GROUP BY stock, LEFT(tradetime,10)
WHERE "some where clause that will select fewer dates and/or stocks, - I'll will vary (but generally include a stock = N and I don't think I'll get into troubles with this part)
MIN and MAX is quite simple
But how do I do open and close?
-
May 3, 2008, 05:11 #2
- Join Date
- May 2008
- Posts
- 15
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Actually even better would be to get the weighted average if there are more trades at the same date and time, i.e. (count1*price1 + count2*price2 + count3*price3 ...) / (count1+count2+count3+...)
But it's not that important if it is much harder to do and/or put a much bigger stress on the database
-
May 3, 2008, 08:19 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code:SELECT daytradedata.stock , daytradedata.tradedate , daytradedata.opentime , opentrade.price AS openprice , daytradedata.closetime , closetrade.price AS closeprice FROM ( SELECT stock , DATE(tradetime) AS tradedate , MIN(price) AS minprice , MAX(price) AS maxprice , MIN(tradetime) AS opentime , MAX(tradetime) AS closetime FROM trades WHERE stock = 937 AND tradetime >= '2008-04-01' AND tradetime < '2008-05-01' GROUP BY stock , DATE(tradetime) ) AS daytradedata INNER JOIN trades AS opentrade ON opentrade.stock = daytradedata.stock AND opentrade.tradetime = daytradedata.opentime INNER JOIN trades AS closetrade ON closetrade.stock = daytradedata.stock AND closetrade.tradetime = daytradedata.closetime
-
May 4, 2008, 11:39 #4
- Join Date
- May 2008
- Posts
- 15
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you, - you're the man!
Bookmarks