Aggregate intraday data <p>As from my previous question, I have this query to perform data aggregation from intraday to daily data</p> <pre><code>SELECT symbol, date, MAX(high) AS high, MIN(low) as low, SUM(volume) as volume, (select open from A2A a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time limit 1) as open, (select close from A2A a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time desc limit 1) as close FROM A2A a1 WHERE date &gt;= date_sub(CURDATE(), interval 100 day) GROUP BY symbol, date ORDER BY symbol, date; </code></pre> <p>but this return less then 100 daily aggregated rows since it use calendar days.</p> <p>1) How to modify this query to get 100 latest records?</p> <p>2) How to aggregate to 5 minute data instead of daily (i.e., first row aggregates data from 0900 up to 0904, then from 0905 up to 0909, etc.)?</p> <p>I use mySQL 5.6.11, data stored are as follow</p> <p><img src="https://i.stack.imgur.com/NKmdr.png" alt="enter image description here"></p> <p>I store stock market data 1 minute per row, I have 511 1 minute rows for every day (from 0900 up to 1730).</p>