I have a MySql table consisting of daily stock quotes (open, high, low, close and volume) which I'm trying to convert into weekly data on the fly. So far, I have the following function, which works for the highs, lows, and volume:
SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;
I need to select the first instance of _open in the above query. So for example, if there was a holiday on Monday (in a particular week) and stock market opened on Tuesday, _open value should be selected from the Tuesday that's grouped into its week. Similarly, the close value should be the last _close from that week.
Is it possible to select something like FIRST() and LAST() in MySql so that the above could be wrapped up within a single SELECT rather than using nested select queries?
Here's my table's create statement to get an idea of the schema:
delimiter $$
CREATE TABLE `mystockdata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol_id` int(11) NOT NULL,
`_open` decimal(11,2) NOT NULL,
`_high` decimal(11,2) NOT NULL,
`_low` decimal(11,2) NOT NULL,
`_close` decimal(11,2) NOT NULL,
`_volume` bigint(20) NOT NULL,
`add_date` date NOT NULL,
PRIMARY KEY (`id`),
KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$
Update: There are no nulls, wherever there's a holiday/weekend, the table does not carry any record for that date.
FIRST
andLAST
as you described don't exist in MySQL. It may be possible with one query, but it depends on your table schema… Update your post. – Jason McCreary