0

have the below query:

select
   hr,
   red  
from
   (select
      hour(event_datetime) hr,
      max(red_kills) - (select
         max(red_kills)  
      from
         vwmatchall  
      where
         date_format(event_datetime,"%Y-%m-%d %H")  = date_format(date_sub(event_datetime, interval 1 hour), "%Y-%m-%d"))    as red  
   from
      vwmatchall  
   where
      date(event_datetime) = '2015-10-09'  
   group by
      hour(event_datetime)) k

the data is structured as such:

enter image description here

the idea is to take the current max() value of a given column, red_kills in the above example, and subtract it from the max() value of the same column, but one hour previous. it's basically to show the difference by hour. so even if one date doesn't have data for all 24 hours, it would still show data for the hours it does have. any idea what's wrong with the query?

Stephen K
  • 697
  • 9
  • 26
  • If you are looking for hint on how to do it then try to implement lag function in MySQL see this URL you will have an idea http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql – narendra Oct 15 '15 at 03:48
  • This part looks wrong to me: `where date_format(event_datetime,"%Y-%m-%d %H") = date_format(date_sub(event_datetime, interval 1 hour), "%Y-%m-%d"))` because you are comparing string to string but using a different format on each side of the equals operator. – Turophile Oct 15 '15 at 05:41

0 Answers0