I have a database of minute by minute stock prices and I would like to
summarize daily information in a 'daily' table, which contains
information about the max, min, range of prices, total volume, etc...
This works so far:
CREATE TABLE daily (
ticker VARCHAR(8) not null,
date DATE not null,
minPrice FLOAT not null,
maxPrice FLOAT not null,
range FLOAT not null,
volume INTEGER not null,
PRIMARY KEY (date,ticker)
);
INSERT INTO daily
SELECT ticker, trade_date, MIN(price), MAX(price), MAX(price)-MIN(price),
SUM(volume)
FROM short_quote
WHERE trade_date<'2001-06-25'
GROUP BY ticker, TO_DAYS(trade_date);
However, I would also like to get the open and closing prices, ie, the
first and last price for each day. Is there a way of getting the
price where trade_date=max(trade_date) within the given GROUP BY
ticker/day.
Thanks,
John Hunter
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
