Stack Overflow

Group by hour in mysql but include the first value from the hour+1 also

I’m a big fan of Stack Overflow and I tend to contribute regularly (am currently in the top 0.X%). In this category (stackoverflow) of posts I will will be posting my top rated questions and answers. This, btw, is allowed as explained in the meta thread here.

My question was:

Say you have data like this:

  time  value
  10:00   5
  10:15   12
  10:30   15
  10:45   27
  11:00   29

And a query like this:

SELECT MAX(value)- MIN(value), HOUR(time)FROM mytable GROUP BY HOUR(time);

You will get:

 value  time
   22   10

But, I would need it to include the value at 11:00, thus the result being 24 (29-5), and not 22. Is there a way to do this in SQL or do I have no other choice than to do this manually in the code level (so, without the grouping, just fetching the data and manually substracting).

The answer, by ATP_JD, was:

Depending on how consistent your data is, you might be able to do this with a self join, like so:
SELECT HOUR(a.`time`)AS grouper,
GREATEST(MAX(a.value),IFNULL(MIN(b.value),0))- MIN(a.value)AS diff
FROM mytable a
LEFTJOIN mytable b ONIF(HOUR(a.time)<=23, HOUR(a.time)+1,0)= HOUR(b.time)GROUP BY grouper
The LEFT JOIN on the same table allows you to get the next hour’s value for comparison purposes.

http://sqlfiddle.com/#!9/fe72a/16