title: How to calculate percentage increase from previous row/day after complex Group By?
body: <p>I have a table IntradayPrices1Minute where I store <strong>1 minute</strong> timeframe open, high, low and close prices for stocks:</p>
  
  <pre><code>  CREATE TABLE `IntradayPrices1Minute` (
    `ticker` varchar(10) NOT NULL DEFAULT '',
    `datetime` datetime NOT NULL,
    `volume` mediumint(11) unsigned NOT NULL,
    `open` decimal(8,4) unsigned NOT NULL,
    `high` decimal(8,4) unsigned NOT NULL,
    `low` decimal(8,4) unsigned NOT NULL,
    `close` decimal(8,4) unsigned NOT NULL,
    PRIMARY KEY (`datetime`,`ticker`),
    UNIQUE KEY `indxTickerDatetime` (`ticker`,`datetime`) USING BTREE
  )
  </code></pre>
  
  <p>I have successfully build a query where I can calculate the <strong>daily</strong> open, high, low and close prices for those stocks. This is the query:</p>
  
  <pre><code>SELECT 
      ticker,
      DATE(datetime) AS 'Date',
      SUBSTRING_INDEX( GROUP_CONCAT(CAST(open AS CHAR) ORDER BY datetime), ',', 1 ) as 'Daily Open',
      max(GREATEST(open, high, low, close)) AS 'Daily High',
      min(LEAST(open, high, low, close)) AS 'Daily Low',
      SUBSTRING_INDEX( GROUP_CONCAT(CAST(close AS CHAR) ORDER BY datetime DESC), ',', 1 ) as 'Daily Close'
  
  FROM 
      IntradayPrices1Minute
  
  GROUP BY
      ticker, date(datetime)
  </code></pre>
  
  <p>and this is part of the results that this query successfully returns:</p>
  
  <pre><code>ticker  Date        Open    High    Low     Close
  ----    ----------  ------  ------  ------  ------ 
  AAAE    2012-11-26  0.0100  0.0100  0.0100  0.0100
  AAAE    2012-11-27  0.0130  0.0140  0.0083  0.0140
  AAAE    2012-11-28  0.0140  0.0175  0.0140  0.0165
  AAAE    2012-11-29  0.0175  0.0175  0.0137  0.0137
  AAMRQ   2012-11-26  0.4411  0.5300  0.4411  0.5290
  AAMRQ   2012-11-27  0.5100  0.5110  0.4610  0.4950
  AAMRQ   2012-11-28  0.4820  0.4900  0.4300  0.4640
  AAMRQ   2012-11-29  0.4505  0.4590  0.4411  0.4590
  AAMRQ   2012-11-30  0.4500  0.4570  0.4455  0.4568
  </code></pre>
  
  <p>Now the problem is: I want to return a seventh column in the query that calculates for each day the percentage increase/decrease between its close price and the previous day close price.</p>
  
  <p>I have seen similar questions asked on StackOverflow but for situations in which the daily prices are already in a table. I think it is specially complex in my case because the daily prices are obtained at query time after several grouping calculations.</p>
  
  <p>Any help on this would be greatly appreciated.
  Many Thanks.
  Boga</p>